Is it possible to exclude weekends when calculating the time difference in hours between two dates?

Question

Hi,

We have a requirement to calculate the time difference in hours between two dates to exclude weekends, issues arise with corner cases, such as when the start date falls on a weekend and the end date falls on a weekday, or when the start date is on a weekday and the end date is on a weekend. In these scenarios, the calculation is overridden.

Note: The full weekend exclusion is working as expected, but partial weekend exclusions are not functioning correctly.

Start Date

End Date

TimeDifference(Hours)

Actual TimeDiff(Hours)

09/09/2023 1:22:46 AM

09/11/2023 1:28:28 AM

0.10

1.47

09/23/2023 2:39:32 AM

09/25/2023 2:24:31 AM

0.25

2.41

10/25/24 4:53:56 PM

10/26/24 5:08:20 PM

23.77

7.10

12/9/22 5:59:19 PM

12/10/22 5:16:47 PM

24.72

6.01

PradeepGoud · Answer

Initially, we have tried with this calculation CASE WHEN Start Date < End Date AND Status1 <> Status2 THEN CASE WHEN CAST(Start Date AS DATE) = CAST(End Date AS DATE) THEN ROUND( ABS( TIMESTAMPDIFF(SQL_TSI_MINUTE, Start Date, End Date) / 60.0 ), 2 ) ELSE ROUND( ABS( TIMESTAMPDIFF(SQL_TSI_MINUTE, Start Date, End Date) / 60.0 - (48 * FLOOR((TIMESTAMPDIFF(SQL_TSI_DAY, Start Date, End Date) + DAYOFWEEK(Start Date)) / 7)) ), 2 ) END ELSE 0 END After that, we modified the calculation according to the scenarios but were unable to achieve the accurate results CASE WHEN Start Date < End Date AND Status1 <> Status2 THEN ROUND( ABS( TIMESTAMPDIFF( SQL_TSI_MINUTE, CASE WHEN DAYOFWEEK(Start Date) = 7 THEN TIMESTAMPADD(SQL_TSI_DAY, 2, Start Date) WHEN DAYOFWEEK(Start Date) = 1 THEN TIMESTAMPADD(SQL_TSI_DAY, 1, Start Date) ELSE Start Date END, CASE WHEN DAYOFWEEK(End Date) IN (1, 7) THEN TIMESTAMPADD(SQL_TSI_DAY, CASE WHEN DAYOFWEEK(End Date) = 7 THEN -1 ELSE -2 END, End Date) ELSE End Date END ) / 60.0 - (2 * 24 * FLOOR((TIMESTAMPDIFF(SQL_TSI_DAY, Start Date, End Date) + 1) / 7)) ), 2 ) ELSE 0 END Note: We have tried several different approaches to this calculation.

Luis E. Rivas -Oracle · Answer

What are the expressions you are using to calculate the time differences? Have you tried a case statement to do the exclusions?