CODEWITHSUNDEEP
sql-server
r.baton
r.baton

Reputation: 65

SQL server week ending Thursday range

I'm trying to show the DateTimeRaised field as a week ending Thursday. The FkIssueGroupID isn't unique but each row should have the same date. The MAX is being used to bring back a single row.

Select distinct w.fkIssueGroupID
,[FormDateTimeRaised] =MAX ([DateTimeRaised])
,[WEnd Raised - Thur]= max (CONVERT (date, DATEADD(dd, 5 - (DATEPART(dw, [DateTimeRaised])), [DateTimeRaised]) ))

Whilst the week ending value for Thursday is correct the week days contained within it don't line up i.e.:

Week ending Thursday will have a date of the 21/05/20 (Thursday) but the date values covered by the data are 18/05 to 24/05 (Mon-Sun). I want the date range covered to be 15/05 to 21/05 (Fri-Thur). How do i get the date range to shift from Mon-Sun to Fri-Thur?

Upvotes: 0

Views: 91

Answers (1)

Wouter
Wouter

Reputation: 2976

You can set the first day of the week to Friday like this:

SET DATEFIRST 5;

Upvotes: 1

Related Questions