asked on How do I get the date of a particular day in the week based on another date?
In my database, I've got a "modified_date," representing the last time someone modified that particular row. Let's say, for example, the date is 2021-05-09. That's a Sunday. I need the date of the previous week's Monday, Tuesday, Wednesday, Thursday, etc. so, Monday would be May 3rd, Tuesday would be May 4th etc.
I need the dates of the week associated with whatever date is in the "modified_date" column.
How would I do that?
I need the dates of the week associated with whatever date is in the "modified_date" column.
How would I do that?
MySQL Server
Last Comment
PortletPaul
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Figured it out!
This gives me my Monday:
(SELECT DATE_ADD((SELECT DATE_SUB(DATE(t.LAST_MODIFIED_DT), INTERVAL DAYOFWEEK(t.LAST_MODIFIED_DT)-1 DAY)), INTERVAL 1 DAY)),
...with t.LAST_MODIFIED_DT being the date that I was basing everything on.
Thanks!
This gives me my Monday:
(SELECT DATE_ADD((SELECT DATE_SUB(DATE(t.LAST_MODIFIED_DT), INTERVAL DAYOFWEEK(t.LAST_MODIFIED_DT)-1 DAY)), INTERVAL 1 DAY)),
...with t.LAST_MODIFIED_DT being the date that I was basing everything on.
Thanks!
Not sure why you have "select" (twice) in your solution, and if you subtract 2 you can avoid using date_add()
try this small simlification:
try this small simlification:
with t as (
select curdate() as LAST_MODIFIED_DT union all
select curdate() - interval 1 day union all
select curdate() - interval 2 day union all
select curdate() - interval 3 day union all
select curdate() - interval 4 day union all
select curdate() - interval 5 day union all
select curdate() - interval 6 day
)
select
LAST_MODIFIED_DT
, DATE_SUB(DATE(t.LAST_MODIFIED_DT), INTERVAL DAYOFWEEK(t.LAST_MODIFIED_DT)-2 DAY) as the_monday
from t
Always a treat, sir!
If I'm tracing your logic, I think we're close, but I don't if we're quite there.
"some_field" would be the date the row in question was last modified. It could be a Sunday, it could be a Tuesday. The challenge is to use whatever date is in that field and establish the week that corresponds to that date.
So, say it was Tuesday of this week, which is 05-11-2021. That would be value the "modified_date" column. I need the date of every day in that week.
Monday: 05/10/2021
Tuesday: 05/11/2021
Wednesday: 05/12/2021
etc.
My quandary is that I don't know how to establish the week that corresponds to a date. In this example, since it's so close to the present, perhaps it's not that challenging. But if I'm looking at 02/12/2021 as the "modified_date" value, then I'm stuck.
Make sense?