Need help with date and string data comparison problem 1

[southbeach]

I just stumbled onto a situation where data stored in DB (MySQL) for a date field is set as VARCHAR(10). Dates are stored as mm/dd/YYYY.

Since the new year, queries stopped working (where dates are used). This is, of course, because if you compare literal string 01/01/2009 against 08/20/2008, 01/01/2009 will be of a lesser value thus falling out of date range.

I need a solution to this and I am not sure of a swift and secure solution. These are the options I think I have:

(1) Modify query scripts and do data manipulation outside the MySQL query command
(2) Add date field to table, transfer string date to date field, remove string date field, rename date field
(3) Use available functions to do the work for me strtotime() (or something like that) but I am not sure this is an option at all

Any thoughts?

Thank you all in advance for your assistance.

--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[vacunita]

Your best option, though maybe the less practical, however the most necessary, would be to change all your date varchar fields to actual date fields.

Creating a new field and running a query such as should take care of it easily and painlessly:

UPDATE mydates SET realdate=STR_TO_DATE(chdate,'%m/%d/%Y');

where realdate is the name of the new field that is of type Date, and chdate is the name of your varchar date holding your date information.





----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[southbeach]

Which is option (2) on my OP - I feel that this is the option that offers a more secure and proper solution for the long run.

Your query, however, is great ... Thank you so very much!



--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[southbeach]

OK - I added the column and populated it using above query, that worked pretty nice. I just realized I now need to change the data entry script to make sure date is entered or converted to proper format.

As of this moment, dates are entered as mm/dd/YYYY; I fear that if I attempt to save $_POST['date'] with value of 01/15/2009 to DB field with date data type, the end result will be something like 0000-00-00 (hence your query).

That being said, I need to modify my scripts to use STR_TO_DATE(_) when saving and change back when showing on the screen ... Oh boy, hole just got deeper.


--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[vacunita]

You don;t need to modify your query, just modify your input value using PHP.

$dateparts=explode("/",$_POST['date']);
$mydate=$dateparts[2]. "-" . $dateparts[1] . "-" . $dateparts[0];

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.