[SOLVED] non-standard date format comparison - help

[AndyB]

I managed to force ISO standard date formats for user entries in a database, but forgot that the client can also enter dates.  Unsurprisingly, the Australian client has entered dates (in a different field) in the Oz-style dd/mm/yyyy.

 

I need a query that effectively chooses records where Oz-style date is between two ISO-style dates:

 

SELECT ... WHERE iso_date1>Oz_date and iso_date2<Oz_date ORDER by Oz_date

 

Nothing is jumping out of the ref manual that seems to apply.  I'm sure it's blindingly obvious, but not to me.

[Daniel0]

Would it be possible to use something like

DATE_FORMAT(Oz_date, '%Y-%m-%d') AS oz2iso_date

and then compare the ISO dates to that?

[Barand]

try

 

WHERE STR_TO_DATE(oz_date, '%d/%m/%Y') BETWEEN '$iso1' AND '$iso2' ...

[AndyB]

Thanks, Barry.  After the query failed miserably during localhost testing, I decided to move it to the client's server and it worked. The production server control panel declines to reveal what MySQL release is running.

[Barand]

Have you tried

 

VERSION()

Returns a string that indicates the MySQL server version.

mysql> SELECT VERSION();

        -> '3.23.13-log'

 

Note that if your version string ends with -log this means that logging is enabled.