select query on Dates stored as varchar

From: "Yosef Haas" <yosef(at)karatedepot(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: select query on Dates stored as varchar
Date: 2010-08-30 20:32:54
Message-ID: AFF1FA3F7F2841ED9915AE6D15EFACA6@DJLPKJK1
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Thank you,

Yosef Haas

yosef(at)karatedepot(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2010-08-30 20:44:56 Re: select query on Dates stored as varchar
Previous Message Mark Rostron 2010-08-30 20:18:35 plpgsql cursor syntax question