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

Soldato
Joined
8 Mar 2005
Posts
3,609
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 :)
 
Soldato
Joined
28 Oct 2006
Posts
12,456
Location
Sufferlandria
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
 
Soldato
OP
Joined
8 Mar 2005
Posts
3,609
Location
London, UK
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
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.
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:
Soldato
Joined
28 Oct 2006
Posts
12,456
Location
Sufferlandria
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.
 
Soldato
OP
Joined
8 Mar 2005
Posts
3,609
Location
London, UK
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.
Makes no difference how I spin it around, it still returns <Unable to read data>

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
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:
Soldato
Joined
6 Mar 2008
Posts
10,078
Location
Stoke area
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.
 
Soldato
Joined
6 Mar 2008
Posts
10,078
Location
Stoke area
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.
 
Man of Honour
Joined
26 Dec 2003
Posts
30,837
Location
Shropshire
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
 
Soldato
OP
Joined
8 Mar 2005
Posts
3,609
Location
London, UK
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.
 
Soldato
Joined
8 Oct 2003
Posts
2,843
Location
Glasgow
My last job involved loads of two way integration with many external systems and literally every system wants dates presented in a different format :|
 
Back
Top Bottom