Number of weekdays between two days - StartDate question

Transact-SQL
1

I need to calculate the number of weekdays between a StartDate and EndDate. I use the following which works fine but my StartDate needs to be the 'TradeDate' in the table, and not a specific date set for the StartDate. How can I change the 'SET @StartDate' correctly to pick the 'TradeDate' for each record in the table?

Declare @StartDate DATEtime
Declare @EndDate DATEtime
SET
@StartDate = ? ? ?
SET
@EndDate = GETDATE()
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) -1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -(
CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END
) -(
CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END
) as DaysOutstanding,
DealTicketReference,
PortfolioCode
from
tbl
where
StatusId = '1'
and Type = '0'

2

hi
hope this helps :slight_smile:

use weekday function !!
https://blog.sqlauthority.com/2012/11/25/sql-server-find-weekend-and-weekdays-from-datetime-in-sql-server-2012/

check if weekday .. if it is SUM other wise ignore

sum ( case when weekday 1 else 0 end )

1 Like