0

I am trying to subtract the date from the given date. While subtracting if a weekend (Saturday & Sunday) is in-between, I need to ignore and subtract.

If my input is "2023.03.20" and I'm subtracting 3 days, the expected output would be "2023.03.15". But in my case it's returning "2023.03.17".

I was trying like

DECLARE @datum date  
DECLARE @CalculatedTargetDate date
SET @datum = 
    CASE  
        WHEN DATENAME(DW, CONVERT(DATETIME, '2023-03-20', 102)) = 'Saturday' 
            THEN (CONVERT(DATETIME, '2023-03-20', 104) -1)
        WHEN DATENAME(DW, CONVERT(DATETIME, '2023-03-20', 102)) = 'Sunday' 
            THEN (CONVERT(DATETIME, '2023-03-20', 104) -2)
        ELSE 
            '2023-03-20'
    END
SET @CalculatedTargetDate = DATEADD(day, -3, @datum )
select @CalculatedTargetDate
Thom A
  • 88,727
  • 11
  • 45
  • 75
moor
  • 45
  • 6
  • Tip: `( @@DateFirst + DatePart( weekday, SampleDate ) - 1 ) % 7 + 1` will always return an integer from `1` to `7` with `1` corresponding to Sunday regardless of the setting of `DateFirst` or `Language`. – HABO Mar 08 '23 at 14:06

1 Answers1

1

I hope that this is along the lines of what you are after. One solution that spans multiple months, accumulating non-weekend days is to sum up all non-weekend days and then apply the result to a DATEADD.

DECLARE @StartDate DATETIME= '2008/09/01'
DECLARE @EndDate DATETIME = '2008/10/31'

;WITH R1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
R2(N) AS (SELECT 1 FROM R1 a, R1 b),
R3(N) AS (SELECT 1 FROM R2 a, R2 b), 
Tally(Number) AS (SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM R3)


SELECT  
     SUM(CASE WHEN DATENAME(DW, CONVERT(DATETIME, DATEADD(DAY, Tally.Number, @StartDate), 102)) IN ('Saturday','Sunday') THEN 0 ELSE 1 END) AS NumberOrWorkDaysBetween   
FROM
    Tally
WHERE
    DATEADD(DAY,Tally.Number, @StartDate ) <= @EndDate
Ross Bush
  • 14,648
  • 2
  • 32
  • 55