SQL DateDiff without weekends and public holidays
Solution 1
Try This:
SELECT evnt.event_id,
evnt.date_from,
evnt.date_to,
DATEDIFF(DD, evnt.date_from, evnt.date_to)
- (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2)
- CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END
+ CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff
- (SELECT COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to)
FROM events AS evnt
the uncomment should be a Subquery
--- COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to
like this:
- (SELECT COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to)
Solution 2
Kajiyama,
Try this:
SELECT evnt.event_id,
evnt.date_from,
evnt.date_to,
DATEDIFF(DD, evnt.date_from, evnt.date_to)
- (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2)
- CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END
+ CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff
-(SELECT COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to)
FROM events AS evnt
Looks like you were missing the SELECT
statement before the COUNT(*)
Solution 3
Here is the diffrent answer with WITH common_table_expression (CTE)
;with t as
(
select COUNT(*) as cnt FROM public_holidays
WHERE date_from BETWEEN evnt.date_from AND evnt.date_to
)
SELECT evnt.event_id,
evnt.date_from,
evnt.date_to,
DATEDIFF(DD, evnt.date_from, evnt.date_to)
- (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2)
- CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END
+ CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff
- (select cnt from T)
FROM events AS evnt
Solution 4
( I see this is already answered, but I'll throw this out anyways...)
Might not be a bad idea to have a calendar table with each day as a row, as opposed to a separate public_holidays table. Check out the article SQL Server Calendar Table for a demo and downloadable T-SQL code. Includes a couple of follow-on articles for querying and fiscal years.
Kajiyama
BY DAY: I work as crazy BY NIGHT: I do the same as I do BY DAY
Updated on June 26, 2022Comments
-
Kajiyama about 2 years
I am looking for solution how to select number of days between two dates without weekends and public holidays.
So far I have this:
SELECT evnt.event_id, evnt.date_from, evnt.date_to, DATEDIFF(DD, evnt.date_from, evnt.date_to) - (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2) - CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END + CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff --- COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to FROM events AS evnt
everything works fine untill I uncomment section:
- COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to
What I am trying to achieve is to get number of working days in date range. Problem is with last step, where I am trying to substract all public holiday days from this range.
Can anyone help with this last step? It seems, that I am doing something wrong, but I cant figure out what.
Thank you in advance