[RESOLVED] Date comparison with string

rulian

Ok, I have an issue

I have a date in a mysql database that was entered as a varchar field rather then a date formatted field.

So the dates in the database look like so:

03/28/06
11/28/06
4/02/06

so either MM/DD/YY or M/DD/YY

problem is now I need to do date in ranges from lets say
03/01/06 - 07/01/06

now, i can extract all the days and do that in PHP, but how woudl I go about doing this in mysql?

cgraz

I'm not sure you can do dates in a range with varchar fields holding your date information. You might be able to break the date up into pieces, convert that into a valid date, then compare.

Why not just convert the varchar into a date field?

bradgrafelman

I second cgraz's last question - if you're storing a date, it only makes sense to use a DATE column. Would you use an INT column to store letters?

As if the name of the column - DATE - isn't enough to convince you, note that you can use the simple "BETWEEN ... AND ..." syntax in a query to do what you're wanting, rather than a bunch of string-splitting conversions.

rulian

not and option guys,
the database is already live and I have no option to convert it to another field type.
i know that setting the type to date would be the obvious and best choice, but unfortunately i have to work with strings rather then field types.

alimadzi

Your best bet is to add another column of type DATE and write a script to take each VARCHAR date, convert it, and store it in the new column. Then change the rest of your app to use the new DATE column.

If that's "not an option" for whatever reason, write a function to take your VARCHAR dates and return a PHP timestamp. It would be kludgy but it'll work.

bradgrafelman

Well, you could do a query like so:

SELECT col1, col2
FROM myTable
WHERE STR_TO_DATE(dateColumn, '%c/%d/%y') BETWEEN '2006-03-01' AND '2006-07-01'

Note that this is untested, because I always use DATE columns (still no chance you can change that? You can always run a query to automatically convert the existing data...)

rulian

bradgrafelman: thank you that worked perfectly
I'd rather not touch the information in the database at all, i'm not the only developer on the project, and since I'm adding to the site's already existing functionality, i dont want to change anything and have something esle break on me.

thank you guys for all your help though