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

[darkgen]

I'm struggling to find the right syntax in order to get the result of difference in days between two dates on oracle sql.

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

 

[darkgen]

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!

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.

We use DATEDIFF in TSQL, quick search brings back this: https://stackoverflow.com/questions/28406397/datediff-function-in-oracle

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

 

[darkgen]

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.

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:

    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

    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

    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)

    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

 

[darkgen]

HAH, my edit!

 

[darkgen]

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.