Deriving Previous Saturday Date

Bobby P 231 Reputation points
2021-01-04T20:17:00.223+00:00

I have this T-SQL in a SSIS Package that derives the previous Saturday's Date.

--
DECLARE
@StartDate_In DATETIME = NULL

;

-- Job ===> AbbVie - Synthroid Delivers - Data Pass Files

-- + Scheduled to run Saturday Mornings at 3AM

SET @StartDate_In = '2020-12-26 03:00:00.000'
--SET @StartDate_In = '2021-01-02 03:00:00.000'
--SET @StartDate_In = '2021-01-09 03:00:00.000'
--SET @StartDate_In = '2021-01-16 03:00:00.000'
--SET @StartDate_In = '2021-01-23 03:00:00.000'
;

SELECT DATEADD(DAY,6,(DATEADD(WEEK,(DATEPART(WK,@StartDate_In)-1),DATEADD(YEAR,DATEPART(YEAR,@StartDate_In)-1900, 0)) - 4 -
DATEPART(DW,DATEADD(WEEK,(DATEPART(WK,@StartDate_In)-1),DATEADD(YEAR,DATEPART(YEAR,@StartDate_In)-1900, 0)) - 4) +1 )) AS [StartDate_In]
;

This works fine and dandy for the past year 2020. Why won't this same logic work for 2021?!?

12/26/2020 properly gives me 12/19/2020. However 01/09/2021 gives me 01/09/2021

Thanks for your review and am hopeful for a reply. Just a lot of complicated Date Math to get the previous week's Saturday's Date...but maybe it has to be that way for Month-End and rolling into the next month. Sure doesn't seem as though it works for Year-End and rolling into the next year.

Developer technologies | Transact-SQL
{count} votes

4 answers

Sort by: Most helpful
  1. Viorel 122.7K Reputation points
    2021-01-04T21:44:55.42+00:00

    Check the next formula:

    set datefirst 7
    select cast(dateadd(day, -datepart(weekday, @StartDate_In), @StartDate_In) as date)
    
    0 comments No comments

  2. Bobby P 231 Reputation points
    2021-01-04T22:05:30.247+00:00

    This will get me the previous Saturday at Midnight which is what I want...but how do I get the previous day then at Midnight?

    --
    DECLARE
    @StartDate_In DATETIME = NULL

    ;

    -- Job ===> AbbVie - Synthroid Delivers - Data Pass Files

    -- + Scheduled to run Saturday Mornings at 3AM

    SET @StartDate_In = '2020-12-26 03:00:00.000'
    --SET @StartDate_In = '2021-01-02 03:00:00.000'
    --SET @StartDate_In = '2021-01-09 03:00:00.000'
    --SET @StartDate_In = '2021-01-16 03:00:00.000'
    --SET @StartDate_In = '2020-01-23 03:00:00.000'
    ;

    -- Returns the previous Saturday Date
    -- @StartDate_In = '2020-12-26 03:00:00.000' THEN 2020-12-19 00:00:00.000
    SELECT DATEADD(DAY, 5, DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, -1, @StartDate_In)) - 1, 0))

    0 comments No comments

  3. EchoLiu-MSFT 14,621 Reputation points
    2021-01-05T02:51:15.79+00:00

    Hi @Bobby P

    Welcome to the Microsoft TSQL Q&A Forum!
    I watched the entire thread and the question you posted seems to have been answered. Then you asked new and different questions.
    Try:

        DECLARE  
        @StartDate_In DATETIME = NULL  
        SET @StartDate_In ='2021-01-09 03:00:00.000'  
        SELECT DATEADD(DAY, 4, DATEADD(WEEK, DATEDIFF(WEEK, 0, @StartDate_In), 0))  
    

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. Visakh 211 Reputation points Volunteer Moderator
    2021-01-07T09:15:48.233+00:00

    @Bobby P

    Easiest way would be to use a server independent logic like below which will work regardless of your datefirst setting and works for any year

    declare @dt date ='20160321'  
      
    --declare @dt date ='20200511'  
      
    --declare @dt date ='20210115'  
      
    --declare @dt date ='20230124'  
      
    --declare @dt date ='20250712'  
      
    SELECT DATEADD(dd,-CASE WHEN  DATEDIFF(dd,0,@dt) % 7 > 5 THEN  DATEDIFF(dd,0,@dt) % 7 - 5 ELSE (DATEDIFF(dd,0,@dt) % 7) + 2 END,@dt)  
    

    It will work for any dates regardless of whether its past, present or future

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.