How do I get the first day of the week of a date in mysql?

Question

Suppose I have 2011-01-03 and I want to get the first of the week, which is sunday, which is 2011-01-02, how do I go about doing that?

The reason is I have this query:

select    YEAR(date_entered) as year,    date(date_entered) as week,   <-------This is what I want to change to select the first day of the week.   SUM(1) as total_ncrs,    SUM(case when orgin = picked_up_at then 1 else 0 end) as ncrs_caught_at_station  from sugarcrm2.ncr_ncr  where  sugarcrm2.ncr_ncr.date_entered > date('2011-01-01')  and orgin in(  'Silkscreen',  'Brake',  'Assembly',  'Welding',  'Machining',  '2000W Laser',  'Paint Booth 1',  'Paint Prep',  'Packaging',  'PEM',  'Deburr',  'Laser ',  'Paint Booth 2',  'Toolpath'  )  and date_entered is not null  and orgin is not null  AND(grading = 'Minor' or grading = 'Major')   and week(date_entered) > week(current_timestamp) -20  group by year, week(date_entered)  order by year   asc, week asc  

And yes, I realize that origin is spelled wrong but it was here before I was so I can't correct it as too many internal apps reference it.

So, I am grouping by weeks but I want this to populate my chart, so I can't have all the beginning of weeks looking like different dates. How do I fix this?

Answer 1

If the week starts on Sunday do this:

DATE_ADD(mydate, INTERVAL(1-DAYOFWEEK(mydate)) DAY) 

If the week starts on Monday do this:

DATE_ADD(mydate, INTERVAL(-WEEKDAY(mydate)) DAY); 

more info