Sql datediff between 2 date exclude sunday only-CodePudding

DECLARE @totaldays INT, 
        @weekenddays INT, 
        @startDate datetime = '2023/01/01', 
        @endDate datetime = '2023/01/08'

SET @totaldays = DATEDIFF(DAY, @startDate, @endDate)  1 
SET @weekenddays = ((DATEDIFF(WEEK, @startDate, @endDate) * 2)   
                    CASE 
                        WHEN DATEPART(WEEKDAY, @startDate) = 1 
                            THEN 1 
                            ELSE 0 
                    END   
                    CASE 
                        WHEN DATEPART(WEEKDAY, @endDate) = 6 
                            THEN 1 
                            ELSE 0 
                    END)

SELECT (@totaldays - @weekenddays) AS Days

I have this code to find the datediff of 2 dates excluding 'Sunday'.

The result is 5, but it should be 6.

If @startDate datetime = '2023/01/01', @endDate datetime = '2023/01/07' result = 6, the answer becomes correct.

How can I make it become the correct query?

CodePudding user response：

It might be possible to make this CASE WHEN construct correctly run, but in my opinion, this is really bad to read. I would do following:

DECLARE @startdate datetime
DECLARE @enddate datetime
DECLARE @count int

SET @count = 0
SET @startdate = '2023/01/01'
SET @enddate = '2023/01/08'

WHILE @startdate <= @enddate
  BEGIN
    IF DATEPART(WEEKDAY,@startdate) <> 1
      SET @count = @count   1
      SET @startdate = DATEADD(d,1,@startdate)
    END
SELECT @count AS Days;

The simple idea is to use a counter which will be increased by one only in case the day is no sunday.

Try out here: db<>fiddle

CodePudding user response：

Can you try this :

I have some small adjustment in your query

DECLARE @totaldays INT,
        @weekenddays INT, 
        @startDate datetime = '2023/01/01', 
        @endDate datetime = '2023/01/08'

SET @totaldays = DATEDIFF(DAY, @startDate, @endDate)  1 
SET @weekenddays = (select datediff(day,@startDate,@endDate) /7  
case   
    when datepart(WEEKDAY,@startDate) = 1 then 1
    when datepart(WEEKDAY,@startDate) > datepart(Weekday,@endDate) then 1
    else 0
end)

SELECT (@totaldays - @weekenddays) AS Days

You can try it from here : https://dbfiddle.uk/vBYoOWW6