davidahines davidahines - 5 months ago 14
MySQL Question

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

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

If you need to handle weeks which start on Mondays, you could also do it that way. First define a custom FIRST_DAY_OF_WEEK function:

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:

SELECT FIRST_DAY_OF_WEEK('2011-01-03');

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.

And WEEKDAY:

Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

Comments