Deriving Previous Saturday Date

Question

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.

Answer 1

Check the next formula:

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

Answer 2

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))

Answer 3

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.

Answer 4

@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