Oracle SQL - How to find the difference between two date variables-CodePudding

I am trying to find the longest period of time in between the order date and ship date times. I understand the method of getting it, but I am just unsure about it. Im using the JustLee Book Order Database for this one Here is my code

SELECT o.ORDER#, o.ORDERDATE, o.SHIPDATE, o.SHIPCITY, o.SHIPSTATE
FROM ORDERS o;
WHERE (SELECT

I would have liked to give you a better result of my query but I am stuck on the dates. Any help is good help. Tank you for your time

CodePudding user response：

To find the difference between two DATE data type values, just subtract one from the other.

SELECT ORDER#,
       ORDERDATE,
       SHIPDATE,
       SHIPCITY,
       SHIPSTATE,
       SHIPDATE - ORDERDATE AS days_between_ship_and_order
FROM   ORDERS;

If you want to find the maximum, then from Oracle 12, you can order the results in descending order of difference and use FETCH FIRST ROW WITH TIES to row(s) find only the largest difference.

SELECT ORDER#,
       ORDERDATE,
       SHIPDATE,
       SHIPCITY,
       SHIPSTATE,
       SHIPDATE - ORDERDATE AS days_between_ship_and_order
FROM   ORDERS
ORDER BY days_between_ship_and_order DESC
FETCH FIRST ROW WITH TIES ONLY;