datetime comparison

[johnie]

Hi. I have a varchar field in which the data is like
"7/16/2003 7:47:00 PM"

Is it possible for example to make any comparisons with mysql's datetime functions.

I tried all the possible queries (TO_DAYS(NOW()), DATE_SUB, etc) to find which items have that date field set in the last 30 days without getting any results.

And i think the problem might not be the varchar type, but the AM/PM in the data.

Can i do something?

Thanks,
John

[Sxooter]

If you're going to play with timestamps, you should be storing them in something made for the job (datetime, timestamp, etc...) Then it's much easier. Otherwise you can cast the varchars first:

select cast('2003-10-10 12:15:00' as datetime) + interval '1 month';