Link to home
Start Free TrialLog in
Avatar of MeridianManagement
MeridianManagementFlag for United States of America

asked on

changing date format to compare dates on the fly in mysql query

I have a database where the dates are in mm.dd.yy format. I need to compare dates to other dates that are in the same format. All I need to know is if one date comes before the other. How can I compare them using a query when they are in this format?
I want to do
 
select * from table where date > date2

Open in new window

Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Are the fields actually stored as date data type:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

Or is this a character field that stores a date?
Avatar of MeridianManagement

ASKER

no, they're stored as varchar in "mm.dd.yy" format. So, "07.01.09" is an example of July 1st 2009. Otherwise I wouldn't even need to ask this question to compare dates as it would be very simple.
If it is string, you can try using STR_TO_DATE function on both dates and then the comparison as you have codes will work just fine.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
beautiful, thank you, worked like a charm!