I know it would be best to change the data type of the column (I didn't initially create the table), but doing so would be require changes to several application that use it and I'd like to avoid that if possible. Thanks, Yosef Haas yosef@xxxxxxxxxxxxxxx -----Original Message----- From: Pavel Stehule [mailto:pavel.stehule@xxxxxxxxx] Sent: Monday, August 30, 2010 4:45 PM To: Yosef Haas Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: select query on Dates stored as varchar Hello 2010/8/30 Yosef Haas <yosef@xxxxxxxxxxxxxxx>: > 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@xxxxxxxxxxxxxxx > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
