How to get last 5 weeks start and end dates

Question

How to get last 5 weeks start and end dates

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
July 14, 2022 at 12:10 am

#4063019

Example - When i run the query today 7/13/2022
I am looking for start and end dates to be June 11, 2022 that is saturday to July 9, 2022 saturday. I am looking for start date to be saturday from the last 5 weeks from today.
July 23, 2022 at 9:36 am

This was removed by the editor as SPAM
July 26, 2022 at 7:36 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63501 More actions · Answer 1

Would have sworn there was a handy article by Perry Whittle or Lynn Pettis that covers useful date functions, but I can't find it right now... but this one looks promising:

Exploring the DATE Functions in SQL – SQLServerCentral

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

mcfarlandparkway wrote:

Example - When i run the query today 7/13/2022
I am looking for start and end dates to be June 11, 2022 that is saturday to July 9, 2022 saturday. I am looking for start date to be saturday from the last 5 weeks from today.

Actually, that's a span of 4 weeks and 1 day if the high date is inclusive. Hopefully, the high date will be used in an exclusive manner so that you get a nice, full 4 weeks including all the times that occur on the Friday, which is the last day of the 4th week.

This does it.

 SELECT SatLo = DATEADD(wk,-4,s.SatHi),s.SatHi
   FROM (VALUES(DATEADD(dd,DATEDIFF(dd,5,GETDATE())/7*7,5)))s(SatHi)
;

The Table Value Constructor in the FROM clause does a DATEDIFF between whatever today is and a known Saturday (the "5" is the Date Serial Number for the 6th of January, 1900, which is a Saturday).

That gets divided by 7 to return the number of whole weeks since the known Saturday.

That gets multiplied by 7 to get us back to the number of days contained in those whole weeks.

That gets added to Date Serial Number 5 (again, we know that's a Saturday) and that gives us the Saturday on or before today. That's the "high Saturday at the end of the range of dates you wanted).

Then the SELECT subtracts 4 weeks from that to get the "low Saturday", which is the start of your desired range and also returns the high Saturday previously calculated in the FROM clause.

As a bit of a sidebar, you can't use DATEDIFF(wk) for this because, in order to keep DATEDIFF() deterministic, that had to ALWAYS base DATEDIFF(wk) on Sundays.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

@mcfarlandparkway

Did that work for you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ratbak Ten Centuries Points: 1358 More actions · Answer 4

MySQL functions --

DATE_SUB(CURDATE(), INTERVAL 7 DAY)

-- won't be of much use on SQL Server 2019.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

ratbak wrote:

MySQL functions --
DATE_SUB(CURDATE(), INTERVAL 7 DAY)
-- won't be of much use on SQL Server 2019.

Yeah... that's just spam for an "Assignment writing" service to help people cheat on their university assignments. Worse than that type of unethical crap, it's the wrong answer even in MySQL and so they just proved they aren't worth the powder to blow them to hell. I reported it as spam.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)