TimeStampDiff in Hours Excluding Weekends

JobDeLuna

Hi,

Anyone can help how can I get the timestampdiff in hours between two dates:

1. ("Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE")
2. ("Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE")

and weekend should be excluded in the calculation?

I have this below that shows timestampdiff in days, however I want to see the result in hours.

Thanks!

CASE WHEN DAYOFWEEK("Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE") > DAYOFWEEK("Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE") THEN CASE WHEN DAYOFWEEK("Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE") = 7 THEN TIMESTAMPDIFF(SQL_TSI_DAY, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE") - (Floor(TIMESTAMPDIFF(SQL_TSI_DAY, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE")/7)*2)-1 ELSE TIMESTAMPDIFF(SQL_TSI_DAY, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE") - (Floor(TIMESTAMPDIFF(SQL_TSI_DAY, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE")/7)*2)-2 END ELSE TIMESTAMPDIFF(SQL_TSI_DAY, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE") - (Floor(TIMESTAMPDIFF(SQL_TSI_DAY, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE")/7)*2) END

Raghavendra Raghunath-Oracle

Hi @JobDeLuna In OTBI or DV you can use the following function if the columns have timestamp on them, for example:

TIMESTAMPDIFF(SQL_TSI_HOUR,"Worker"."Assignment Start Date","Worker"."Assignment End Date")

Raghu

RichardChan

The above would still include weekends in the count

RichardChan

I found this similar post that cover off some ideas https://forums.oracle.com/ords/apexds/post/date-difference-between-2-dates-excluding-weekends-1201

JobDeLuna

Hi, @RichardChan.

Thanks for your response however it seems like the calculation post some errors.

I have tried this calculation and it works,

(timestampdiff(SQL_TSI_DAY, (DATE '2024-03-06'), (DATE '2024-03-11'))) / 7.0 * 5.0

the result is correct as 4 days

but when I tried to make the first date as Monday and the last date as Friday, it gives me incorrect result.

(timestampdiff(SQL_TSI_DAY, (DATE '2024-03-04'), (DATE '2024-03-08'))) / 7.0 * 5.0

the result is 3 days only which should be 5 days