1

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.

  • 1
    Sounds like you have strings, not dates. – Colin 't Hart Jun 17 '18 at 18:20
0

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.
0

If I were you, I would use a view to transform the date with the DATE_FORMAT function:

http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

https://dev.mysql.com/doc/refman/5.7/en/create-view.html

To avoid refactoring your SELECTs, rename the table out of the way, and use the original name for the view. As long as you keep the view simple like that, it will perform about as well as the physical table (negligible overhead) on anything except for anything that might enjoy an index on that date field.

Because the DATE_FORMAT function make the view non-writeable, you still have to refactor your write statements to use DATE_FORMAT to go the other way.

0

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.

Then, I would suggest, you don't have Dates at all, but Strings that hold values that look like dates. This is a recipe for lots [and lots] of Trouble.

One of the Many Rules of Databases: Dates have no format.

At least, none that you or I would need to worry about, anyway.

If you're going to change this table at all (and you should) then change it to use proper Date fields. Yes, you will probably have to change some other stuff as well, but that's just par for the course and you will gain the ability to proper, "Date" things with the fields, rather than mucking about with them as Strings or having to put conversion functions into all your queries which of course, slow them to a crawl, having to Table Scan everything, feeding each row in turn into the conversion function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.