Days between two dates in Oracle SQL.

Something like this:

1. SELECT TO_DATE (date to, 'yyyy-MM-dd') - TO_DATE ( date from, 'yyyy-MM-dd') FROM DUAL;

LOL, thanks for your answer, I was able to find this solution on google too. But I am new to oracle so I need to know who to plug in my  OrderDate and ShippingDate from OrderSales file to the code you provided.
Thanks.

Is this what you are looking for?

SELECT TO_DATE(ShippingDate, 'yyyy-MM-dd') - TO_DATE (OrderDate, 'yyyy-MM-dd') FROM OrderSales;

Select allOpen in new window

yes, that works, now I have the next question, how do I use  NVL command to use PackingDate when ShipingDate is null?

Dates don't have a 'format'.  If the data types are actually 'date', then subtracting them natively gives you the number of days.

You only use TO_DATE when converting from a varchar2 or some other character data type.

I would suggest COALESCE.  It is in the ANSI standard:

select coalesce( ShipingDate, PackingDate) - OrderDate from ordersales

Select allOpen in new window

You accepted an answer that I assume means the date columns are varchar2.  I wouldn't store dates as strings.

If not, I would strongly suggest you not use to_date on date columns.

Thanks

slightwv ,
I like what you propose.
Abdul, did you try slightwv solution?

select coalesce( ShipingDate, PackingDate) - OrderDate from ordersales

Select allOpen in new window

Was the column format varchar2 or Date?

No, I have not, the column is DateTime, since the results I am seeing from your solution works for me so I didn't try the other solution.
 
Thanks.

@slightwv, your solution gives me   "-0.5027777777777777777777777777777777777778"  as a result.

>>the column is DateTime

In Oracle there is no DateTime.  The Date data type has the time portion built in.  You need to decide if the actual time portion is populated and if you need to be concerned about it.

Again, dates in Oracle don't have a format until they are displayed in 'string' format.

This is controlled by the parameter NLS_DATE_FORMAT.

>>your solution gives me

I'm guessing that is incorrect?  

If the data types are dates, my query should return pretty close to the other.  I say pretty close because the to_date on a date strips the time portion from an Oracle date.  There are better ways.

I have no way to see it -0.5 is correct or not since I don't have your data.  To get a negative number your orderdate is greater than the other date in the coalesce.

If you can provide sample data and expected results I can provide a tested example.

Here is a quick fiddle that shows the two queries return the same values with simple data:
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c14f6e5337b193a590bc410d12be01c3
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c14f6e5337b193a590bc410d12be01c3

Yes, your query returns almost the same result and the reason that you have mentioned for the negative number is correct (I understand that). I just have to add a code to round the numbers into a full number. Thanks for your explanation about the DateTime type data, its good learning.

Thanks.

>> I just have to add a code to round the numbers into a full number.

Whole numbers, which way do you want to go?  If you get 5.1 is that 6 or 5?

Look at CEIL or FLOOR.  ROUND is always an option if you want 5.6 to be 6 and 5.4 to be 5.

Just to add to slightwv's solution, if you don't care about the time of day, remove it and just do date-math.

select trunc(coalesce( ShipingDate, PackingDate)) - trunc(OrderDate) from ordersales

Select allOpen in new window

That drops the time portion of the date column and subtracts the dates.

Thanks everyone for your knowledge sharing, it was great learning exercise.