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:
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
sugarcrm2.ncr_ncr.date_entered > date('2011-01-01')
and orgin in(
'Paint Booth 1',
'Paint Booth 2',
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
If you need to handle weeks which start on Mondays, you could also do it that way. First define a custom
DELIMITER ;; CREATE FUNCTION FIRST_DAY_OF_WEEK(day DATE) RETURNS DATE DETERMINISTIC BEGIN RETURN SUBDATE(day, WEEKDAY(day)); END;; DELIMITER ;
And then you could do:
For your information, MySQL provides two different functions to retrieve the first day of a week. There is DAYOFWEEK:
Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard.
Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).