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

User_4MU80

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)
		Start Date	09/09/2023 1:22:46 AM			End Date	09/11/2023 1:28:28 AM			TimeDifference(Hours)	0.10			Actual TimeDiff(Hours)	1.47
		Start Date	09/23/2023 2:39:32 AM			End Date	09/25/2023 2:24:31 AM			TimeDifference(Hours)	0.25			Actual TimeDiff(Hours)	2.41
		Start Date	10/25/24 4:53:56 PM			End Date	10/26/24 5:08:20 PM			TimeDifference(Hours)	23.77			Actual TimeDiff(Hours)	7.10
		Start Date	12/9/22 5:59:19 PM			End Date	12/10/22 5:16:47 PM			TimeDifference(Hours)	24.72			Actual TimeDiff(Hours)	6.01

Luis E. Rivas -Oracle

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

User_4MU80

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.