Page 1 of 1

str_to_date() syntax queries

Posted: Mon Dec 03, 2007 4:04 am
by Eric Praline
G'day peeps,

Hopefully this is a simple query, but I 'm not sure exactly how the str_to_date() query works...

I have a database which has a VARCHAR field (called RemoveBy) containing dates in the format DD/MM/YY and DD/MM/YYYY, and I need to run a query to find records where the RemoveBy date is greater than the current date ie the date hasn't yet been reached.

So, having been browsing t'internet I found something to check an exact known date -
SELECT * from tablename WHERE str_to_date(RemoveBy, '%d/%m/%Y') = str_to_date('06/12/2007', '%d/%m/%Y')";
- which I thought should find any records with a RemoveBy date of 06/12/2007 - however it doesn't work, so is my syntax correct? And I suppose I'd have to add an extra query to find dates like 06/12/07 as well?

And then, how do I check that against the current date? curdate() returns a value as YYYY-MM-DD, so can I convert it to check a different date format?

Cheers.

Posted: Mon Dec 03, 2007 9:34 am
by feyd
I'd suggest altering the column to a normal DATE field, or at least to the normal formatting as that compares with a simple expression.

Posted: Mon Dec 03, 2007 10:07 am
by Eric Praline
OK... so is there a quick and easy way to convert all the current VARCHAR records to DATE type ones?
Luckily there's only about 150 records in the db so far, so wouldn't take me too long if I had to do it manually... but I'd prefer a quick MySQL command instead! :D

Posted: Mon Dec 03, 2007 10:42 am
by feyd
You may be able to simply alter the field, but I'm not entirely sure.

The safest way is to add a temporary field that is a DATE type, run an update with enough logic to massage the existing information into proper format then alter the table againt to remove the old field and rename the temporary one. Then of course you have to alter your code to send the proper format.

Posted: Wed Dec 05, 2007 5:34 am
by Eric Praline
Well I've managed to find out how to use PHP to change a date format entered as a string (ie from an HTML form) to a format suitable to be entered into the MySQL database... and also how to convert it back to display again in another HTML form in a more readable format - who on earth decided that YYYY-MM-DD was a sensible date format? :)

Anyway, I couldn't figure out how to get str_to_date() to convert all my date strings to a date field format, so did it manually instead. Luckily I only had to change a dozen or so...

Thanks for your help though! :D

Maybe it's me being a thicky, or the manual isn't particularly well-written with good examples of doing something which SHOULD be fairly simple... personally I blame the manual :wink: