Search code examples
mysqliso8601date-formatphp

numeric representation of the day of the week MySql Vs PHP

In my PHP code I'm using date("N") ISO-8601 numeric representation of the day of the week 1 (for Monday) through 7 (for Sunday).

Now when I wanted to make a query to search for a specific date.

I found that in MySQL:

  • The date_format using %w will return (0=Sunday..6=Saturday)

  • The DAYOFWEEK(date) will return (1 = Sunday .. 7=Saturday)

Is there a fast way to fix this issue without doing some PHP ?

Solution

  • Trying to match...

    PHP date('N'):

Mon Tue Wed Thu Fri Sat Sun
1   2   3   4   5   6   7

    To one of these...

    MySQL DATE_FORMAT(col, '%w'):

Mon Tue Wed Thu Fri Sat Sun
1   2   3   4   5   6   0

MySQL DAYOFWEEK(col):

Mon Tue Wed Thu Fri Sat Sun
2   3   4   5   6   7   1

    I would use a CASE statement with DATE_FORMAT as you only have to worry about Sunday.

    SELECT
    CASE
        WHEN DATE_FORMAT(col, '%w') = 0
            THEN 7
        ELSE DATE_FORMAT(col, '%w')
   END AS dayofweek
...