SQLTeam.com | Weblogs | Forums

Current Day of the week from Current week of the year


I need help to find current day from current week of the year, and start and end dates of the current week.
For eg – Today is Monday, April 8th, 2019. 1st day of the week.
This is the 15th week. I am able to find this individually, but would like 1 SQL statement if possible.

My output should be 1, 04/08/2019, 04/14/2019.



Depending on where you are, Sunday is first day of week, not Monday

declare @d date = getdate()

select @d, DATEPART(dw, @d), DatePart(Week, @d)



I used @base_date only so that it's easy to test for dates other than today, rather than hard-coding GETDATE() in all the calcs:

DECLARE @base_date date
DECLARE @most_recent_Monday date

SET @base_date = GETDATE()

SET @most_recent_Monday = DATEADD(DAY, -DATEDIFF(DAY, 0, @base_date) % 7, CAST(@base_date AS date))

SELECT @most_recent_Monday AS most_recent_Monday, (DATEDIFF(DAY, DATEADD(YEAR, DATEDIFF(YEAR, 0, @base_date), 0), @most_recent_Monday) + 1)/7 + 1 AS week#