CODEWITHSUNDEEP
sqldatabaseoracle-databaseselectcase
Ponzaro
Ponzaro

Reputation: 149

Oracle - Condition on a datetime

I am working on this DB that contains informations on shop orders in a plant. I am using this query that retrieves order data, using the closing date as condition. It extracts order that are closed in a time range of two weeks.

select order.id, order.end_date, material.id, material.description
from plant.order inner join plant.material
on order.material = material.id
where order.end_date <= to_date('26/10/2018 06:00:00', 'DD/MM/YYYY HH24:MI:SS') 
where order.end_date <= to_date('12/10/2018 06:00:00', 'DD/MM/YYYY HH24:MI:SS')

Since the workday is divided in shifts (for example, the workday 26th October starts from the 26/10 6:00 and ends in 27/10 6:00 to cover the 24 hours), I need to manage this thing.

Is it possible to use a condition that checks if the hour is < 6, and if the condition is true, it writes the correct value in another column? Or is there a better way to manage it?

order.id      order.end_date     material.id      material.description 
--------      --------------     -----------      --------------------
 1             26/10/2018 05:00    1                Lorem Ipsum
 2             26/10/2018 07:00    2                Lorem Ipsum


order.workday (new column)
-------------
 25/10/2018
 26/10/2018

In this case the order id 1 should result of date 25/10/2018, while order id 2 should result of date 26/10/2018.

I hope that I have been clear.

Thanks

Upvotes: 1

Views: 104

Answers (2)

Fahmi
Fahmi

Reputation: 37473

Try below using CASE WHEN

    select 
       order.id, 
       order.end_date, 
       material.id, 
       material.description,
       case when TO_CHAR (order.end_date, 'HH24:MI:SS')<'06:00:00' then order.end_date- interval '1' day else order.end_date end as workday
    from plant.order inner join plant.material,
    on order.material = material.id
    where order.end_date <= to_date('26/10/2018 06:00:00', 'DD/MM/YYYY HH24:MI:SS') 
    where order.end_date <= to_date('12/10/2018 06:00:00', 'DD/MM/YYYY HH24:MI:SS')

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

You could simply use TRUNC(order.end_date - 6/24)

WITH t AS (
   SELECT TO_DATE('26/10/2018 05:00' , 'dd/mm/yyyy hh24:mi') AS end_date FROM dual
   UNION ALL SELECT TO_DATE('26/10/2018 07:00' , 'dd/mm/yyyy hh24:mi') AS end_date FROM dual)
SELECT end_date, TRUNC(end_date - 6/24)
from t;


+----------------------------------------+
|END_DATE           |TRUNC(END_DATE-6/24)|
+----------------------------------------+
|26.10.2018 05:00:00|25.10.2018          |
|26.10.2018 07:00:00|26.10.2018          |
+----------------------------------------+

Upvotes: 3

Related Questions