Current day from current week of the year

Question

Current day from current week of the year

MrMister17

SSC Enthusiast

Points: 115
More actions
April 8, 2019 at 2:18 pm

#3017224

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.
My output should be 1, 04/08/2019, 04/14/2019.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

I would do something like this:

DECLARE @date DATETIME = getdate();

SELECT WeekStart = CAST(DATEADD(DAY,f.Dt+1, @date) AS date),
WeekEnd = CAST(DATEADD(DAY,f.Dt+8, @date) AS date)
FROM (VALUES(DATEPART(WEEKDAY,@date)-1)) AS w(Wd)
CROSS APPLY (VALUES(-IIF(w.Wd=0,7,w.Wd))) AS f(Dt);

Returns:

WeekStart WeekEnd
---------- ----------
2019-04-08 2019-04-15

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jonathan Szeto SSC Enthusiast Points: 120 More actions · Answer 2

Here's what I would do:

SET DATEFIRST = {whatever day of the week your week starts};

SELECT
CurrentDayOfCurrentWeek = (datepart(dd, getdate()) - 1) % 7,
WeekStartDate = dateadd(dd, -(datepart(dw, getdate() - 1)), cast(getdate() AS date)),
WeekEndDate = dateadd(dd, 7 - datepart(dw, getdate()), cast(getdate() AS date))
;

The SET DATEFIRST is only necessary if your system's week isn't the same as the business. Otherwise you can ignore it.

garry.lovesey SSC Veteran Points: 299 More actions · Answer 3

This sounds like the perfect use for a Calendar table. You can find an example here: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 4

We have a few articles on calendar tables as well: https://www.sqlservercentral.com/search/calendar+table

I noticed you mentioned this is the 15th week, but didn't include that in the output. Is that correct?