CODEWITHSUNDEEP
mysqldateselecttime
davidahines
davidahines

Reputation: 4094

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?

Upvotes: 51

Views: 106331

Answers (9)

Kamil Beeli de Belfort
Kamil Beeli de Belfort

Reputation: 9

  SELECT MIN(DATE*given_date*) FROM *table_name*

This will return when the week started at for any given date.

Keep the good work going!

Upvotes: -2

Cem G&#252;ler
Cem G&#252;ler

Reputation: 635

select '2011-01-03' - INTERVAL (WEEKDAY('2011-01-03')+1) DAY;

returns the date of the first day of week. You may look into it.

Upvotes: 9

Павел П
Павел П

Reputation: 132

If the week starts on Monday do this:

DATE_SUB(mydate, INTERVAL WEEKDAY(mydate) DAY)

Upvotes: 0

Ghanshyam Nakiya
Ghanshyam Nakiya

Reputation: 1712

Week starts day from sunday then get First date of the Week and Last date of week

SELECT  
  DATE("2019-03-31" + INTERVAL (1 - DAYOFWEEK("2019-03-31")) DAY) as start_date,  
  DATE("2019-03-31" + INTERVAL (7 - DAYOFWEEK("2019-03-31")) DAY) as end_date

Week starts day from Monday then get First date of the Week and Last date of week

SELECT  
  DATE("2019-03-31" + INTERVAL ( - WEEKDAY("2019-03-31")) DAY) as start_date, 
  DATE("2019-03-31" + INTERVAL (6 - WEEKDAY("2019-03-31")) DAY) as end_date

Upvotes: 13

Egene
Egene

Reputation: 301

If week starts on Monday

  SELECT SUBDATE(mydate, weekday(mydate));

If week starts on Sunday

  SELECT SUBDATE(mydate, dayofweek(mydate) - 1);

Example:

SELECT SUBDATE('2018-04-11', weekday('2018-04-11'));

2018-04-09

SELECT SUBDATE('2018-04-11', dayofweek('2018-04-11') - 1);

2018-04-08

Upvotes: 18

Paul Ishak
Paul Ishak

Reputation: 1093

This works form me

Just make sure both dates in the below query are the same...

SELECT ('2017-10-07' - INTERVAL WEEKDAY('2017-10-07') Day) As `mondaythisweek`

This query returns: 2017-10-02 which is a monday,

But if your first day is sunday, then just subtract a day from the result of this and wallah!

Upvotes: 1

Ruslan
Ruslan

Reputation: 10147

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

Upvotes: 121

Kenny_D
Kenny_D

Reputation: 51

This is a much simpler approach than writing a function to determine the first day of a week.

Some variants would be such as
SELECT DATE_ADD((SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1)DAY),INTERVAL 7 DAY) (for the ending date of a query, such as between "beginning date" and "ending date").
SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1) DAY (for the beginning date of a query).

This will return all values for the current week. An example query would be as follows:
SELECT b.foo FROM bar b
WHERE b.datefield BETWEEN
(SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1) DAY)
AND
(SELECT DATE_ADD((SELECT CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1)DAY),INTERVAL 7 DAY))

Upvotes: 2

St&#233;phane
St&#233;phane

Reputation: 3934

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).

Upvotes: 45

Related Questions