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.
str_to_date() syntax queries
Moderator: General Moderators
-
Eric Praline
- Forum Commoner
- Posts: 32
- Joined: Wed Aug 29, 2007 8:37 am
-
Eric Praline
- Forum Commoner
- Posts: 32
- Joined: Wed Aug 29, 2007 8:37 am
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.
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.
-
Eric Praline
- Forum Commoner
- Posts: 32
- Joined: Wed Aug 29, 2007 8:37 am
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!
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
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!
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