str_to_date() syntax queries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Eric Praline
Forum Commoner
Posts: 32
Joined: Wed Aug 29, 2007 8:37 am

str_to_date() syntax queries

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Eric Praline
Forum Commoner
Posts: 32
Joined: Wed Aug 29, 2007 8:37 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Eric Praline
Forum Commoner
Posts: 32
Joined: Wed Aug 29, 2007 8:37 am

Post 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:
Post Reply