I am using MySQL and having issues getting the end of the week date from a DATETIME column, where the end of the week is considered Sunday,

My table looks like this:

I am needing to get the week for each line where the week ends on Sunday. The time isn't needed. So the end result for 2021-01-01 would show 2021-01-03 since that week ends on Sunday. Does anyone know what function to use for this?

CodePudding user response：

Here's an elaboration (I hope) of Akina's suggestion in the comment:

SELECT *,
       dt   INTERVAL 6 DAY add6 /*add 6 day ahead*/,
       DAYNAME(dt   INTERVAL 6 DAY) dn6 /*6 day ahead dayname*/,
       dt   INTERVAL (6 - wkd) DAY nxtsun /*add 6 day ahead then subtract weekday value from date column*/,
       DAYNAME(dt   INTERVAL (6 - wkd) DAY) nxtsundn
FROM
(SELECT *, 
       DATE(date) dt,
       DAYNAME(date) dn,
       WEEKDAY(date) wkd
FROM mytable) A;

Let's take the second row from your data sample to illustrate what is happening. The base query above:

SELECT *, 
       DATE(date) dt,
       DAYNAME(date) dn,
       WEEKDAY(date) wkd
FROM mytable

Will return the following.

Note that the WEEKDAY(date) (aliased as wkd in the table) returns 4. Which means it's Friday. According to the docs, WEEKDAY() function returns like the following:

0 = Monday
1 = Tuesday
2 = Wednesday
3 = Thursday
4 = Friday
5 = Saturday
6 = Sunday

Adding 6 day interval to the current WEEKDAY() result goes to the day before next same dayname of the current date value. So WEEKDAY(2021-01-01) which is on Friday, becomes 2021-01-07 which is on Thursday after being added with 6 day ahead. With a subtraction of the pervious obtained WEEKDAY() value, the operation becomes DATE INTERVAL (6 - 4) DAY, which effectively becomes DATE INTERVAL 2 DAY.

Here's a fiddle