Current Date to Week Start Date

  • I need the the start date if the week given a date and which day starts a week. Here is an example:

    Current Date: 02/07/2019
    Week Starts: Tuesday (2)
    Week Starts Date: 02/05/2019

    My goal is to have a T-SQL statement that I can pass the Current Date and Week Starts parameters in and it returns Week Starts Date.

    Thanks in advance for the help!

  • SQL Espo - Monday, February 11, 2019 12:42 PM

    I need the the start date if the week given a date and which day starts a week. Here is an example:

    Current Date: 02/07/2019
    Week Starts: Tuesday (2)
    Week Starts Date: 02/05/2019

    My goal is to have a T-SQL statement that I can pass the Current Date and Week Starts parameters in and it returns Week Starts Date.

    Thanks in advance for the help!

    Huh?  Weeks usually start on a Sunday.  2/7/2019 was a Thursday, so the date that the week started on was 2/3/2017
    Are you really looking for the 7 day period that a date falls into, given the data and the first day of the 7 day period?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • No. I am working on a system where the client wants a group of users to choose when their week starts....I cant good into super detail due to and NDA. For example, a user may want his week to start on a Tuesday and another might want it to begin on Thursday. The week itself would still run for 7 days. From my example

    Current Date: 02/07/2019
    Week Starts: Tuesday (2)
    Week Starts Date: 02/05/2019
    Week End Date: 02/12/2019

  • SQL Espo - Monday, February 11, 2019 1:14 PM

    No. I am working on a system where the client wants a group of users to choose when their week starts....I cant good into super detail due to and NDA. For example, a user may want his week to start on a Tuesday and another might want it to begin on Thursday. The week itself would still run for 7 days. From my example

    Current Date: 02/07/2019
    Week Starts: Tuesday (2)
    Week Starts Date: 02/05/2019
    Week End Date: 02/12/2019

    This is throwing me.  
    Week Starts: Tuesday (2)

    What does the (2) represent?  This is not consistent with the SQL built in date time functions.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John - Monday, February 11, 2019 1:17 PM

    SQL Espo - Monday, February 11, 2019 1:14 PM

    No. I am working on a system where the client wants a group of users to choose when their week starts....I cant good into super detail due to and NDA. For example, a user may want his week to start on a Tuesday and another might want it to begin on Thursday. The week itself would still run for 7 days. From my example

    Current Date: 02/07/2019
    Week Starts: Tuesday (2)
    Week Starts Date: 02/05/2019
    Week End Date: 02/12/2019

    This is throwing me.  
    Week Starts: Tuesday (2)

    What does the (2) represent?  This is not consistent with the SQL built in date time functions.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql?view=sql-server-2017

  • You could do something like this:


    DECLARE @Week_Start AS Int
    DECLARE @Current_Date AS Date
    DECLARE @Week_Start_Date AS Date
    DECLARE @Day_Diff Int

    SET @Week_Start = 3
    SET @Current_Date = GetDate()

    SET @Day_Diff = DatePart(WeekDay, @Current_Date) - @Week_Start
    If @Day_Diff < 0 SET @Day_Diff = @Day_Diff + 7

    SET @Week_Start_Date = DateAdd(day, -@Day_Diff, @Current_Date)

    SELECT
        @Week_Start AS Week_Start,
        @Current_Date AS [Current_Date],
        DatePart(WeekDay, @Current_Date) AS Current_WeekDay,
        @Day_Diff AS Day_Diff,
        @Week_Start_Date AS Week_Start_Date

    That determines the weekday number for the given date, determines the difference between that and the weekday start number specified, Adjusts by a week if the diff is negative, then adds the result back to the original date to get the week start date. 

  • Here are the formulas to get the values you want. It's basically adding weeks to known dates. I'm using days instead of weeks to be able to add or substract days in the same operation. The -1 is to adjust the date as shown on the second query, and the +6 is simply -1+7.

    DECLARE @CurrentDate  DATE = '20190207',
       @WeekStarts  INT = 2;

    SELECT DATEADD( DD, DATEDIFF(DD, @WeekStarts, @CurrentDate)/7*7-1, @WeekStarts),
       DATEADD( DD, DATEDIFF(DD, @WeekStarts, @CurrentDate)/7*7+6, @WeekStarts);

    SELECT WeekStarts,
      CONVERT( datetime, WeekStarts) AS IntegerDate,
      DATENAME(DW, WeekStarts)   AS ActualDay,
      DATENAME(DW, WeekStarts-1)  AS AdjustedDay
    FROM (VALUES(1), (2), (3), (4), (5), (6), (7))x(WeekStarts);

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • SQL Espo - Monday, February 11, 2019 1:25 PM

    Michael L John - Monday, February 11, 2019 1:17 PM

    SQL Espo - Monday, February 11, 2019 1:14 PM

    No. I am working on a system where the client wants a group of users to choose when their week starts....I cant good into super detail due to and NDA. For example, a user may want his week to start on a Tuesday and another might want it to begin on Thursday. The week itself would still run for 7 days. From my example

    Current Date: 02/07/2019
    Week Starts: Tuesday (2)
    Week Starts Date: 02/05/2019
    Week End Date: 02/12/2019

    This is throwing me.  
    Week Starts: Tuesday (2)

    What does the (2) represent?  This is not consistent with the SQL built in date time functions.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql?view=sql-server-2017

    This may get you started, although I think I would create a date calendar table.


    DECLARE @CurrentDate Datetime = '02/07/2019'
    DECLARE @WeekStarts smallint = 2

    DECLARE @DaysBack smallint

    IF @WeekStarts NOT IN (1, 2, 3, 4, 5, 6, 7) Begin
        PRINT 'Error' --RAISEERROR
    End
    Else Begin
        SET DATEFIRST @WeekStarts
        SET @DaysBack = (SELECT DATEPART(weekday, @CurrentDate) * -1) + 1
        SELECT DATEADD(day, @DaysBack, @CurrentDate)
    End

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

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

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