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

2648 views sql
-4

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?

answered question

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?

Could you provide some sample data and expect result?

Hi, I mean when passing 2 dates in DATEDIFF function, it should not count Thursday and Friday.

2 Answers

7

If I understand correctly you can try to use CASE WHEN with datepart check datepart(weekday,GETDATE()) didn't contain 5 and 6 which is Thursday & Friday.

SELECT CASE WHEN datepart(weekday,GETDATE()) NOT IN (5,6) AND  
                  datepart(weekday,TTHI_CREATED_ON) NOT IN (5,6)
                THEN DATEDIFF(DAY, TTHI_CREATED_ON, GETDATE())  END
FROM TT_TAPE_ISSUE_HD

SQLFIDDLE

posted this
5

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)

posted this

Have an answer?

JD

Please login first before posting an answer.

Ads

Categories