How do you find out the DayOfTheWeek given a datetime in MySql ?

Question

Given a datetime column in mysql 5, I want to find out the day of the week ?

But the function DAYOFWEEK returns 1 for Sunday.

I want 1 to denote monday, 2 tuesday etc.. and 7 to denote Sunday.

Is there a one-liner (that can be embedded in a larger SQL statement) that implements this function ?

f(x) => y

such that:
   f(1) = 7
   f(n) = n-1 for n in range [2,7]

Answer 1

You can try CASE WHEN n = 1 THEN 7 ELSE n -1 END

Answer 2

A purely arithmetic formula is

SELECT ((DAYOFWEEK(myDate) + 5) % 7) + 1 AS MondayBasedDOW

Although in SQL we often solve this with a CASE statement (cf astender's answer)

SELECT CASE WHEN DAYOFWEEK(myDate) = 1 THEN 7 ELSE DAYOFWEEK(myDate) -1 END
       AS MondayBasedDOW

Now... MySQL also provides the function WEEKDAY() which is similar to DAYOFWEEK(), but is

• Monday-based
• Zero-based

i.e. Monday = 0, Tuesday = 1 ... Sunday = 6
So you can simply use

SELECT WEEKDAY(myDate) + 1 AS MondayBasedDOW

but of course that's a lot less fun than using modulo arithmetic ;-)