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

LVL 2
MeridianManagementAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Example usage:
SELECT * 
FROM table 
WHERE STR_TO_DATE(date, "%m.%d.%y") > STR_TO_DATE(date2, "%m.%d.%y");

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
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?
0
 
MeridianManagementAuthor Commented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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
0
 
MeridianManagementAuthor Commented:
beautiful, thank you, worked like a charm!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.