Check the next formula:
set datefirst 7
select cast(dateadd(day, -datepart(weekday, @StartDate_In), @StartDate_In) as date)
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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.
Check the next formula:
set datefirst 7
select cast(dateadd(day, -datepart(weekday, @StartDate_In), @StartDate_In) as date)
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
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))
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.
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