CODEWITHSUNDEEP
sqlsql-servertableau-desktop
Choco La
Choco La

Reputation: 9

Difference of two datetime excluding weekends

I have to datetime fields. Start date is "10/29/22 5:30 44 pm" End date is "10/31/22 2:53 54 pm"

I need to calculate the difference of these two datetime in day,hours,minutes, seconds

It should give me result (0days 14hrs 53min 54sec) because 10/29 and 10/30 is weekend and only Mondays time should be calculated.

I can calculate the days successfully by following function

Datediff(d,startdate,enddate) -datediff(w,startdate,enddate) -datediff(w,startdate,enddate)

It gives me the result as 0 days but I am unable to calculate the desired hours time and seconds properly.

Upvotes: 0

Views: 356

Answers (2)

Ranjit Singh
Ranjit Singh

Reputation: 3735

The script starts by declaring two DATETIME variables @StartDate and @EndDate and setting them to the given start and end dates.

Next, the script calculates the total number of seconds between the start and end dates using the DATEDIFF function with a SECOND interval.

The script calculates the total number of days between the start and end dates, excluding weekends, by subtracting the number of full weeks between the dates (multiplied by 2) from the total number of days. This is done using the DATEDIFF function with a DAY interval and a subquery that counts the number of weekend days between the start and end dates.

The script then checks whether the start and/or end dates fall on a weekend (Saturday or Sunday). If so, it adjusts the start and/or end date and recalculates the total number of seconds accordingly, so that the time during weekends is excluded from the calculation.

Finally, the script calculates the number of hours, minutes, and remaining seconds from the adjusted total number of seconds, and concatenates the results into a string with the desired format using the CONCAT function.

DECLARE @StartDate DATETIME = '2022-10-29 17:30:44'
DECLARE @EndDate DATETIME = '2022-10-31 14:53:54'

DECLARE @TotalSeconds INT
SET @TotalSeconds = DATEDIFF(SECOND, @StartDate, @EndDate)

DECLARE @Days INT
SET @Days = DATEDIFF(DAY, @StartDate, @EndDate)

DECLARE @WeekendDays INT
SET @WeekendDays = (SELECT COUNT(*) FROM (
    SELECT DATEDIFF(DAY, 0, DATEADD(DAY, rn, @StartDate)) AS d
    FROM (
        SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
        ROW_NUMBER() OVER (ORDER BY s1.object_id) - 1 AS rn
        FROM sys.all_objects AS s1
        CROSS JOIN sys.all_objects AS s2
    ) AS x
    WHERE DATENAME(WEEKDAY, DATEADD(DAY, rn, @StartDate)) IN ('Saturday', 'Sunday')
) AS weekends)

SET @Days = @Days - @WeekendDays

IF DATENAME(WEEKDAY, @StartDate) = 'Saturday'
BEGIN
    SET @StartDate = DATEADD(DAY, 2, Cast(@StartDate as Date))
    SET @TotalSeconds = DATEDIFF(SECOND, @StartDate, @EndDate)
END
ELSE IF DATENAME(WEEKDAY, @StartDate) = 'Sunday'
BEGIN
    SET @StartDate = DATEADD(DAY, 1, Cast(@StartDate as Date))
    SET @TotalSeconds = DATEDIFF(SECOND, @StartDate, @EndDate)
END

IF DATENAME(WEEKDAY, @EndDate) = 'Saturday'
BEGIN
    SET @EndDate = DATEADD(DAY, -1, Cast(@EndDate as Date))
    SET @TotalSeconds = DATEDIFF(SECOND, @StartDate, @EndDate)
END
ELSE IF DATENAME(WEEKDAY, @EndDate) = 'Sunday'
BEGIN
    SET @EndDate = DATEADD(DAY, -2, Cast(@EndDate as Date))
    SET @TotalSeconds = DATEDIFF(SECOND, @StartDate, @EndDate)
END

DECLARE @Hours INT
SET @Hours = @TotalSeconds / 3600
SET @TotalSeconds = @TotalSeconds % 3600

DECLARE @Minutes INT
SET @Minutes = @TotalSeconds / 60
SET @TotalSeconds = @TotalSeconds % 60

SELECT CONCAT(@Days, 'days ', @Hours, 'hrs ', @Minutes, 'min ', @TotalSeconds, 'sec') AS 'Duration'

Upvotes: 0

siggemannen
siggemannen

Reputation: 9274

How about something like this:

DECLARE @dtfrom DATETIME = '20221020 17:30'
,   @dtTo DATETIME = '20221031 14:53'
,   @dtFromFixed DATETIME
,   @delta DATETIME

-- Mangle up dtFrom if it's weekend or unaligned with dtTo
SELECT  @dtFromFixed = CASE WHEN dayOfWeekFrom IN(5,6) THEN CAST(CAST(@dtfrom + 7 - dayOfWeekFrom AS DATE) AS DATETIME) 
    WHEN dayOfWeekFrom > dayOfWeekTo THEN @dtfrom + 2
    ELSE @dtFrom
END
FROM    (
    SELECT  (DATEPART(WEEKDAY, @dtfrom) + @@DATEFIRST - 2 ) % 7 AS dayOfWeekFrom
    ,   (DATEPART(WEEKDAY, @dtTo) + @@DATEFIRST - 2 ) % 7 AS dayOfWeekTo
    ) d

-- Set difference
SET @delta = @dtTo - 
    (@dtFromFixed + 2 * (DATEDIFF(DAY, @dtFromFixed, @dtTo) / 7))

-- Return result
SELECT  DATEDIFF(DAY, 0, @delta) AS days
,   CAST(@delta AS TIME) AS times

This snippet should handle various DATEFIRST settings.

The idea is to just add 2 days to fromDate for every week difference between from and to date. To handle edge case where fromDate is on weekend, i first transfer it to first day of next week. To handle if it's less than one week between two dates but week day of to date is less than from date, i manually add 2 days first.

Finally i extract date difference with some dating magic.

Caveats:

  • I don't use "proper" dating functions, to rewrite it is left as exercise to the nitpickers/readers
  • I'm not sure it handles toDate being on a weekend correctly. Easiest is to fix the toDate so it falls on correct date. It doesn't handle fromDate > toDate either
  • Very important to test this for various date combinations, because it's likely mess up some edge cases

Upvotes: 0

Related Questions