Search code examples
mysqltime-formatstr-to-date

MySQL Update Field Where Time is more than Specified in Specific Day

I have database table and I want to update some fields where the time is more than 17:15:59 and the day is not Friday

I've made the schema in sqlfiddle

http://sqlfiddle.com/#!9/b8e99/1

And this is the query:

UPDATE saring SET pulang = '12:00:59'
WHERE TIME_FORMAT(STR_TO_DATE(pulang, '%r'), '%T') > '17:15:59' AND DAYNAME(STR_TO_DATE(tanggal, '%a')) != 'Friday'

But I am definitely doing something wrong. I need some help.

Thank you for the help.

Solution

  • First, change the columns types into this:

    tanggal: date
pulang: time

    And change the dates format into something likde: '2015-08-10'.
    This enables you to run query without the Timeformat function like this:

    UPDATE saring SET pulang = '12:00:59'
WHERE pulang > '17:15:59' 
AND DAYNAME(tanggal) != 'Friday'

    The page you made, doesn't seem to update queries, so try this on your localhost server. Here Is a sqlfiddle with SELECT statement:
    SQLFiddle