How can I exclude some days when using DATEDIFF function in SQL Server?

Question

My simple select statement looks like this

SELECT DATEDIFF(DAY, TTHI_CREATED_ON, GETDATE()) FROM TT_TAPE_ISSUE_HD

It works perfectly. I need to exclude Thursday & Friday and display the number of days different. How can I achieve this in SQL Server?

What do yuo mean `I need to exclude Thursday & Friday and display the number of days different`? Do you want to exclude `GETDATE()` or `TTHI_CREATED_ON`? — D-Shih, Nov 05 '18 at 06:46
Hi, I mean when passing 2 dates in **DATEDIFF** function, it should not count Thursday and Friday. — , Nov 05 '18 at 06:51
https://stackoverflow.com/questions/19765962/calculating-days-to-excluding-weekends-monday-to-friday-in-sql-server possible duplicate of — Ameya Deshpande, Nov 05 '18 at 06:54
Check this question, it should help. [Exclude Weekends and get time in SQL](https://stackoverflow.com/questions/46345662/exclude-weekends-and-get-time-in-sql) — Andrey Nikolov, Nov 05 '18 at 06:54

score 0 · Answer 1 · answered Nov 05 '18 at 06:57

0

If you wan't to avoid 2 days from a week which is Thursday & Friday you can use the below query

 SELECT
  (DATEDIFF(dd, TTHI_CREATED_ON,  GETDATE()) + 1)
  -(DATEDIFF(wk, TTHI_CREATED_ON,  GETDATE()) * 2)
  -(CASE WHEN DATENAME(dw, TTHI_CREATED_ON) = 'Thursday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, TTHI_CREATED_ON) = 'Friday ' THEN 1 ELSE 0 END)

answered Nov 05 '18 at 06:57

Sanal Sunny

617
3
9

This is what I wanted. Tank you. – Nov 05 '18 at 07:21

score 0 · Answer 2 · answered Nov 05 '18 at 07:00

Check this link it will provide you more idea,

How to calculate the number of "Tuesdays" between two dates in TSQL?

SELECT (DATEDIFF(DAY, TTHI_CREATED_ON, GETDATE())-
(datediff(day, -4, GETDATE())/7-datediff(day, -3, TTHI_CREATED_ON)/7 
  + datediff(day, -4, GETDATE())/7-datediff(day, -2, TTHI_CREATED_ON)/7 )
  FROM TT_TAPE_ISSUE_HD

How can I exclude some days when using DATEDIFF function in SQL Server?

2 Answers2