SQL - datediff (minutes) exclude weekends

Question

can you help me, I´m using below sql view(which then I´m using in crystal reports). I need there date differences(in minutes), but now I need to exclude weekends. Please help :)

SELECT intwc                             AS wc,
       Datediff(n, start_date, end_date) AS time,
       mh_start_date                     AS date,
       'Repair'                          AS type
FROM   dbo.xxxxxxx

Possible Duplicate [Count work days between](http://stackoverflow.com/questions/252519/count-work-days-between-two-dates-in-t-sql) — rs., Mar 19 '13 at 13:38
Just to confirm, for a difference between `Friday 11:30PM` and `Monday 12:30AM` would you want to see 60 minutes? — bendataclear, Mar 19 '13 at 13:39
I don't think that 'counting work days between' is a good match for this question. — Jonathan Leffler, Apr 15 '13 at 01:10

score 5 · Answer 1 · edited May 23 '17 at 12:08

This is a modifed version of @bendataclear's answer. It calculates weekend minutes directly rather than caculating the days and multiplying by 24*60. It also accounts for all 4 combinations of starting/ending on saturday/sunday

I'm using CONVERT(date,@StartDate) to get the date of @StartDate with a time of 00:00:00, which is then used to calculate the partial Sundays and Saturdays. There are better ways of doing that but I went with the simplest.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2013/03/15 23:30:00'
SET @EndDate = '2013/03/17 00:30:00'


SELECT
(   DATEDIFF(MINUTE, @StartDate, @EndDate)
    - ( DATEDIFF(wk, @StartDate,@EndDate)*(2*24*60)
        -- End on Sunday
        -(CASE WHEN DATEPART(dw, @EndDate)  = 1 THEN 24.0*60-DATEDIFF(minute,CONVERT(date,@EndDate),@EndDate) ELSE 0 END)
        -- Start on Saturday
        -(CASE WHEN DATEPART(dw, @StartDate) = 7 THEN DATEDIFF(minute,CONVERT(date,@StartDate),@StartDate) ELSE 0 END)
        -- End on Saturday
        +(CASE WHEN DATEPART(dw, @EndDate)  = 7 THEN DATEDIFF(minute,CONVERT(date,@EndDate),@EndDate) ELSE 0 END)
        -- Start on Saturday
        +(CASE WHEN DATEPART(dw, @StartDate) = 1 THEN 24.0*60-DATEDIFF(minute,CONVERT(date,@StartDate),@StartDate) ELSE 0 END)
    )
)

For extra marks, /60 this answer by 60 will give you the total hours between two dates and %60 will give you the total minutes. (Results are rounded up) — Master Yoda, Jan 22 '17 at 18:42

score 2 · Answer 2 · edited May 23 '17 at 12:33

2

This answer assumes you want to exclude weekends on a minute basis, also it's based entirely on the answer in this question:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2013/03/15 23:30:00'
SET @EndDate = '2013/03/18 00:30:00'


SELECT
   (DATEDIFF(MINUTE, @StartDate, @EndDate))
  -(DATEDIFF(wk, @StartDate, @EndDate) * (2*24*60))
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN (24*60) ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN (24*60) ELSE 0 END)

edited May 23 '17 at 12:33

Community

1
1

answered Mar 19 '13 at 13:45

bendataclear

3,802
3
32
51

1

The last two lines will subtract the entire 24*60 minutes regardless of what time StartDate or EndDate are. However if for example EndDate is at 0:30 on a Saturday, then there's only 30 minutes that should be subtracted, not 24*60. In your code try changing EndDate to '2013/03/17 00:30:00' and it will give a negative number. – wwv Aug 05 '13 at 19:38

score 0 · Answer 3 · answered Mar 19 '13 at 13:35

SELECT intwc                             AS wc,
   Datediff(n, start_date, end_date) AS time,
   mh_start_date                     AS date,
   'Repair'                          AS type
FROM   dbo.xxxxxxx 
Where DATEPART(dw, start_date) NOT IN (1, 7) and DATEPART(dw, end_date) NOT IN (1, 7)

score 0 · Answer 4 · answered Mar 19 '13 at 14:02

So you do need to be able to juggle some CASE statements to handle all of your edge cases. Here's an example that I put together. The Numbers table is just a tally table, in this case 1 through 30.

CREATE TABLE #times (id INT IDENTITY(1,1), start_stamp DATETIME, end_stamp DATETIME)

INSERT INTO #times
        ( 
          start_stamp ,
          end_stamp
        )
SELECT DATEADD(DAY, -2*Number, CURRENT_TIMESTAMP), DATEADD(DAY, -1*Number, CURRENT_TIMESTAMP)
FROM Common.NUMBERS
WHERE Number < 31

SELECT id, start_stamp, end_stamp,
CASE WHEN DATEDIFF(DAY, start_stamp, end_stamp) < 7 THEN
    CASE WHEN DATEPART(weekday, start_stamp) < DATEPART(weekday, end_stamp)
        THEN DATEDIFF(MINUTE, start_stamp, DATEADD(HOUR, -48, end_stamp))
        ELSE DATEDIFF(MINUTE, start_stamp, end_stamp) END
    ELSE DATEDIFF(MINUTE, start_stamp, DATEADD(HOUR, -48*(DATEDIFF(WEEK, start_stamp, end_stamp)), end_stamp)) END
    + CASE WHEN DATENAME(weekday,start_stamp) IN ('Sunday', 'Saturday') THEN 1440 ELSE 0 END
    + CASE WHEN DATENAME(weekday,end_stamp) IN ('Sunday', 'Saturday') THEN 1440 ELSE 0 END
FROM #times

There may be a more elegant way to do that, but the code allows you to run against the entire result set and calculate per row.

SQL - datediff (minutes) exclude weekends

4 Answers4