Re: select query on Dates stored as varchar

Hello

2010/8/30 Yosef Haas <yosef(at)karatedepot(dot)com>:
> I have a table (transactions) with an order_date field that is varchar(32).
> The data looks like this:
>
>
>
>     order_date
>
> -------------------
>
> 20100623 02:16:40
>
> 20100623 04:32:41
>
> 20100625 04:18:53
>
> 20100625 07:53:24
>
>
>
> In my current database (8.1.4) if I run
>
> select (*) from transactions where order_date > ‘2010-06-24’;
>
> I get:
>
> count
>
> --------
>
> 2
>
>
>
> I’m moving to a new server that has 8.2.11. There, if I run the same query,
> I get
>
> count
>
> --------
>
> 4
>
>
>
>
>
> In both, select (*) from transactions where order_date > ‘20100624’; returns
> 2.
>
>
>
> The newer version does not seem to know that ‘2010-06-24’ is a date the same
> way that ‘20100624’ is.
>
>
>
> Any ideas? Is there somewhere that I can specify that with, or without the
> hyphens, they are both dates?
>

a) check your locales - different locales can make different order
b) use a native data type "timestamp" - your technique isn't best - it
is slower, needs more memory and depends on locale.

Regards

Pavel Stehule

>
>
>
>
> Thank you,
>
> Yosef Haas
>
> yosef(at)karatedepot(dot)com
>
>