Given a date in MySQL, how can I determine the relative start of the week?
I have a date_time
column in my table.
How do I get the date for the start of the week, i.e. the date 2011-01-09 13:44:00
would return 2011-01-07 00:00:00
?
I have looked over MySQL Date functions but wasn't able to figure out how to do it.
I tried something like this...
DATE_ADD( YEAR( NOW( ) ) , INTERVAL WEEK( NOW( ) ) WEEK ) AS `start_of_week`
But all I got was NULL
.
Ho开发者_如何转开发w do I do this?
If you don't mind converting to Date first (from DateTime)
DATE_ADD(mydate, INTERVAL(1-DAYOFWEEK(mydate)) DAY)
Also, the STR_TO_DATE function is a possibility. Feed it your year and week concatenated right together, followed by your desired string-date format, which could then be cast to a datetime.
SELECT STR_TO_DATE('201003 Monday', '%X%V %W');
SELECT DATE_ADD( DATE( NOW() ) , INTERVAL -WEEKDAY( NOW() ) DAY ) AS `start_of_week`
This returns 2011-05-30, so will be good if your first day of week is monday, otherwise you should to substract one more day.
I'm not sure for what did you use week/year
精彩评论