Current day from current week of the year

  • MrMister17

    SSC Enthusiast

    Points: 115

    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.

  • Alan Burstein

    SSC Guru

    Points: 61049

    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: 101

    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: 289

    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: 716215

    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?

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

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