/* Author: adamrob https://adamrob.co.uk / https:adamrob.me Create date: 25OCT2022 Description: Exlude weekends from any datetime query. @since 25OCT2022 adamrob Original Exlude weekends from any datetime query. This solution is tolerant to any localisation settings on target server. 1. Work out the difference in days between the two dates. +1 to be inclusive of the first date. 2. Work out the difference in weeks between the two dates. *2 for the two weekend days. 3. Check for an edge case where if the first date is a sunday it is counted. 4. Check for an edge case where if the last date is a saturday it is missed. Stack overflow answer: https://stackoverflow.com/a/74192360/13842575*/DECLARE@StartQueryDATETIME='10/01/2022';DECLARE@EndQueryDATETIME='10/31/2022';--Work out total days between start and end, exluding weekends.SELECT(datediff(dd,@StartQuery,@EndQuery)+1)-(datediff(wk,@StartQuery,dateadd(dd,1,@EndQuery))*2)-CASEWHENdatename(WEEKDAY,@StartQuery)='Sunday'THEN1ELSE0END-- This includes for start date edge case+CASEWHENDATEname(WEEKDAY,@EndQuery)='Saturday'THEN1ELSE0END-- This includes for end date edge case.
Comments (0)
HTTPSSSH
You can clone a snippet to your computer for local editing.
Learn more.