Mysql – How to select different format of the date in thesql

dateformatMySQLPHP

I have this table and I can't change its format to yyyy-mm-dd because I have so many scripts that are related to this format dd-mm-yyyy. It would be a mess if I change its format.

Id            date

1         01-07-2014
2         02-07-2014
3         03-07-2014
4         05-07-2014
5         07-07-2014
6         14-07-2014
7         18-07-2014
8         19-07-2014
9         21-07-2014
10        01-08-2014
11        02-08-2014
12        03-08-2014

On the php file

$from = '01-07-2014';
$to = '02-08-2014';

I need to update some values from all the dates that are between 01-07-2014 and 01-09-2014 to the format dd-mm-yyyy.

I am using

UPDATE successlog 
   SET successlog.overtime ='00:00'                
WHERE  date >= '$from' 
       AND date <= '$to'

It is not working. I tried using the key between $from and $to. This does not work either.

When the format was yyyy-mm-dd it was working normally, but after I changed the format to dd-mm-yyyy, it is not working.

Best Answer

You'll have to bite the apple. There's no way you can do a proper compare without changing any scripts.

You can spare yourself the trouble to adjust your scripts in the way they are generating the dates, by using STR_TO_DATE() function like this:

UPDATE successlog 
       SET successlog.overtime ='00:00'                
           Where date >= STR_TO_DATE('$from', '%d-%m-%Y') AND 
                 date <= STR_TO_DATE('$to', '%d-%m-%Y')

but you have to adjust your scripts somehow. Store your dates with the proper datatype in your database!

  • Read more about str_to_date() here.
Related Question