T-SQL Date Difference for WeekDays Only

Question

I am trying to get the date difference on a given date, except for week days.

Here is what I have:

SELECT DATEADD (w, -4, GETDATE()) 

This returns 2013-05-04 19: 01: 53.170, which means that it also considers the weekend.

Same for

  SELECT DATEADD (dw, -4, GETDATE()) 

Any help would be appreciated.

Thanks in advance.

Answer 1

I use these functions that return weeks without days off between two dates:

 CREATE FUNCTION [dbo].[DateDiff_NoWeekends]( @date1 DATETIME, @date2 DATETIME ) RETURNS INT AS BEGIN DECLARE @retValue INT SET @date1 = dbo.__CorrectDate(@date1, 1) SET @date2 = dbo.__CorrectDate(@date2, 0) IF (@date1 >= @date2) SET @retValue = 0 ELSE BEGIN DECLARE @days INT, @weekday INT SET @days = DATEDIFF(d, @date1, @date2) SET @weekday = DATEPART(dw, @date1) - 1 SET @retValue = DATEDIFF(s, @date1, @date2) - 2 * 24 * 3600 * ((@days + @weekday) / 7) END RETURN @retValue END GO CREATE FUNCTION [dbo].[__CorrectDate]( @date DATETIME, @forward INT ) RETURNS DATETIME AS BEGIN IF (DATEPART(dw, @date) > 5) BEGIN IF (@forward = 1) BEGIN SET @date = @date + (8 - DATEPART(dw, @date)) SET @date = DateAdd(Hour, (8 - DatePart(Hour, @date)), @date) END ELSE BEGIN SET @date = @date - (DATEPART(dw, @date)- 5) SET @date = DateAdd(Hour, (18 - DatePart(Hour, @date)), @date) END SET @date = DateAdd(Minute, -DatePart(Minute, @date), @date) SET @date = DateAdd(Second, -DatePart(Second, @date), @date) END RETURN @date END 

Here is a demo version of the sql script for all days without days off in April (22).

 SELECT [no weekend days in april] = (dbo.DateDiff_NoWeekends('2013-04-01','2013-05-01') / 3600 / 24) 

Answer 2

The request below indicates the difference only for weekly days, i.e. it is believed that there are no days between two days and subtracts the day off

  DECLARE @StartDate DATETIME, @EndDate DATETIME SELECT @StartDate = '01-July-2008', @EndDate = '30-July-2008' ;WITH DATE (Date1) AS ( SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', @StartDate), '19000101') UNION ALL SELECT DATEADD(DAY, 1, Date1) FROM DATE WHERE Date1 < @EndDate ) SELECT count(*) - ( SELECT count(*) --CONVERT(VARCHAR(15),d1.DATE1 ,110) as [Working Date], --DATENAME(weekday, d1.Date1) [Working Day] from DATE d1 where (DATENAME(weekday, d1.Date1)) in ('Saturday','Sunday') ) --CONVERT(VARCHAR(15),d1.DATE1 ,110) as [Working Date], --DATENAME(weekday, d1.Date1) [Working Day] from DATE d1 where (DATENAME(weekday, d1.Date1)) not in ('Saturday','Sunday') 

please let me know for any clarification

Answer 3

Perhaps I still lack full testing, but it works for me: take the difference in days, and then subtract 2 days for each weekend.

 DateDiff(d, d1, d2) - 2*DateDiff(wk, d1, d2) 

You can also add a function