Oracle SQL - TO_CHAR / TO_DATE get difference in days between 2 dates.

darkgen

darkgen

darkgen

Soldato
Joined
8 Mar 2005
Posts
3,867
Location
London, UK
I'm struggling to find the right syntax in order to get the result of difference in days between two dates on oracle sql.

Code: 
to_char(SCHEDULED_END_DATE, 'YYYY/MM/DD HH24:MI:SS') AS SCHED_END returns 2018/05/31 23:00:00
to_date(to_char(SCHEDULED_END_DATE, 'DD/MON/YYYY"'), 'DD/MON/YYYY') AS Short_date returns 31/05/2018
CURRENT_DATE AS Current_Short_Date returns 08/11/2018
to_date(to_char(SCHEDULED_END_DATE, 'DD/MON/YYYY"'), 'DD/MON/YYYY') - CURRENT_DATE AS Date_Difference
 returns <Unable to read data>

I'm clearly being daft; help :)
 
I'm not familiar with Oracle but it looks like you're converting a date into a char array and back to a date again to get it in the format you want? Surely there's a better way of doing that.
In fact, the format doesnt matter for calculating the difference so you'd probably be better leaving it in the default format for the calculation then formatting correctly later if you need to display the date.

What happens if you do:
SCHEDULED_END_DATE - CURRENT_DATE AS Date_Difference
 
touch said:
I'm not familiar with Oracle but it looks like you're converting a date into a char array and back to a date again to get it in the format you want? Surely there's a better way of doing that.
In fact, the format doesnt matter for calculating the difference so you'd probably be better leaving it in the default format for the calculation then formatting correctly later if you need to display the date.

What happens if you do:
SCHEDULED_END_DATE - CURRENT_DATE AS Date_Difference
Click to expand...
So quite!
Code: 
SCHEDULED_END_DATE - CURRENT_DATE AS DateDiif returns <Unable to read data>
This is what is throwing me. I can clearly output that field as a date value as the previous outputs show, but it throws that result when ignoring any type of conversion.
AHarvey said:
We use DATEDIFF in TSQL, quick search brings back this: https://stackoverflow.com/questions/28406397/datediff-function-in-oracle
Click to expand...
No DATEDIFF in oracle alas. TSQL I'm much better with, oracle sql is a challenge.

EDIT - Just to add; I got to the same page and the solutions did not work for me :(
 
Last edited:
Wait a minute! I've just noticed that your SCHEDULED_END_DATE is set to 2018/05/31 23:00:00. That's in the past, I was expecting a date in the future for the scheduled end date.
When you do a date in the past minus the current date, that'll give a negative difference. I dont know how Oracle would handle that and might be causing an error.
 
touch said:
Wait a minute! I've just noticed that your SCHEDULED_END_DATE is set to 2018/05/31 23:00:00. That's in the past, I was expecting a date in the future for the scheduled end date.
When you do a date in the past minus the current date, that'll give a negative difference. I dont know how Oracle would handle that and might be causing an error.
Click to expand...
Makes no difference how I spin it around, it still returns <Unable to read data>

AHarvey said:
https://ccm.net/faq/8383-equivalent-of-datediff-function-of-the-sql-server-under-oracle

This page states that it's an arithmetic issue:

select DATE1-DATE2 from dual

All Oracle selects need a from so you use a dummy table if you've not got you
Click to expand...
So I'm trying to nest this into the query and I'll be buggered if I know how to do it, even after checking various posts about sub queries in oracle sql. Here is a simplified version of the query in its current form.
Code: 
SELECT     CHANGE_ID, CLASS, SUMMARY, CHANGE_REASON, STATUS, IMPACT, RISK_LEVEL, MANAGER_GROUP, CHANGE_MANAGER,
                      CHANGE_COORDINATOR, SCHEDULED_END_DATE, SCHEDULED_START_DATE
FROM         table
WHERE     multiple_conditions applied
ORDER BY field
The above returns:
Code: 
    CHANGE_IDENT    Normal    Some Activity    Upgrade    Draft    Impact_Level_1    Risk_Level_3    Some_Group    Some_Manager    Some_Cord    30/03/2018    09/03/2018
I'm struggling to then plug in the subquery based on the table DUAL.

I do not have access to the dB itself; so I'm unable to get table properties of the fields but like above even after converting TO_DATE, at least that is what I think I'm doing; it still does not return an expected result.

As you can see the value data for SCHEDULED_END_DATE - SCHEDULED_START_DATE do return date values; so the expectation is it is a TIMESTAMP(or oracle equivalent).

OK, so am getting closer. I'm now subtracting START from END date and getting at least some values
Code: 
    Normal    Upgrade    Draft    30/03/2018    09/03/2018    <Unable to read data>
    Normal    Upgrade    Draft    30/04/2018    29/03/2018    32.25
    Normal    Fix/Repair    Implementation In Progress    01/06/2018    18/05/2018    <Unable to read data>
    Standard    Upgrade    Implementation In Progress    31/05/2018    18/05/2018    <Unable to read data>
    Normal    New Functionality    Implementation In Progress    08/06/2018    21/05/2018    18.3125
And SCHEDULED_START_DATE - SCHEDULED_END_DATE
Code: 
    Normal    Upgrade    Draft    30/03/2018    09/03/2018    <Unable to read data>
    Normal    Upgrade    Draft    30/04/2018    29/03/2018    -32.25
    Normal    Fix/Repair    Implementation In Progress    01/06/2018    18/05/2018    <Unable to read data>
    Standard    Upgrade    Implementation In Progress    31/05/2018    18/05/2018    <Unable to read data>
    Normal    New Functionality    Implementation In Progress    08/06/2018    21/05/2018    -18.3125
Looks to be date format related (i.e. DD/MM or MM/DD) but the data above has rows where I would still expect values ...

EDIT - worked out the correct syntax. Blah oracle!

Select TRUNC(END_DATE - CURRENT_DATE)
Code: 
    Standard    Other    Implementation In Progress    30/11/2018    06/11/2018    -24    21
    Normal    Fix/Repair    Implementation In Progress    20/11/2018    06/11/2018    -14    11
    Normal    Other    Scheduled    28/11/2018    05/11/2018    <Unable to read data>    19
    Normal    New Functionality    Implementation In Progress    04/12/2018    07/11/2018    <Unable to read data>    25
    Normal    Other    Planning In Progress    11/11/2018    08/11/2018    -3    2
 
Last edited:
the fact it's returned XX.xxx indicates that it's including hours and minutes in the calculation as it's returning part of a day.

What happens in you use TRUNC()?

From what I've read it always returned a datatype of date so you could either use TRUNC(SCHEDULED_END_DATE) and TRUNC(SCHEDULED_START_DATE) which i think would remove the time from it and you know it'll be set to date datatype meaning the calculation should be right, or you could just use TRUNC(SCHEDULED_START_DATE - SCHEDULED_END_DATE) and see if that works as expected?

This may help with the read data errors too.
 
lol, always a nice feeling when you get something to work.

I've not worked with Oracle in 20 years and even then I never actually really used it. All TSQL and dread the thought of crossing over.
 
At my last job we ran both TSQL and PL/SQL hours of my time must have been wasted over the course of my time at that place from writing a query in one flavour when I meant the other :o
 
Things like this really throw me for a loop. TSQL is much friendlier and a lot less sensitive when it comes to hacks. Which is basically all my poor addled brain can handle these days.

Thanks all for your support and direction!

Cheers, Paul.
 
My last job involved loads of two way integration with many external systems and literally every system wants dates presented in a different format :|
 
You must log in or register to reply here.
Back
Top Bottom