Given a date in MySQL, how can I determine the relative start of the week?

Question

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.

How do I do this?

Answer 1

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');

Answer 2

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