Looking at a datetime column in mysql 5, do I want to know the day of the week?
But DayoffWeek returns 1 for return on Sunday.
I have to represent 1 Monday, 2 on Tuesday etc. And 7 is to denote Sunday.
Is there a one-liner (which can be embedded in a large SQL statement) that executes this function?
f (x) = & gt; Y as such: f (1) = 7f (n) = n in n -1 category [2,7]
is a purely arithmetic formula
SELECT ((DAYOFWEEK (myDate) + 5)% 7) + 1 In Monday, we often select Case Statement (Answer to CF Attendant) in SQL when DAYOFWEEK (myDate ) = 1 THEN 7 ELSE DAYOFWEEK (myDate) -1 END as MondayBoundDaw
Now ... MySQL also provides the function WEEKDAY () which will be is similar to DAYOFWEEK () , but
- Monday-based
- zero-based
i.e. Monday = 0, Tuesday = 1 ... Sunday = 6
So you can just use
Monday to Monday as SELECT WEEKDAY (myDate) + 1 < / Code>
But definitely using modulo arithmetic is less fun; -)
No comments:
Post a Comment