1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'HTML, Graphics & Programming' started by darkgen, Nov 8, 2018.

  1. darkgen

    Wise Guy

    Joined: Mar 8, 2005

    Posts: 2,177

    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 :)
     
  2. touch

    Capodecina

    Joined: Oct 28, 2006

    Posts: 10,617

    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
     
  3. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 8,997

    Location: Stoke area

  4. darkgen

    Wise Guy

    Joined: Mar 8, 2005

    Posts: 2,177

    Location: London, UK

    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: Nov 8, 2018
  5. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 8,997

    Location: Stoke area

  6. touch

    Capodecina

    Joined: Oct 28, 2006

    Posts: 10,617

    Location: Sufferlandria

    Are you outputting it as the correct data type? Subtracting one date object from another will result in an integer output, not a date output.
     
  7. touch

    Capodecina

    Joined: Oct 28, 2006

    Posts: 10,617

    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.
     
  8. darkgen

    Wise Guy

    Joined: Mar 8, 2005

    Posts: 2,177

    Location: London, UK

    Makes no difference how I spin it around, it still returns <Unable to read data>

    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: Nov 9, 2018
  9. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 8,997

    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.
     
  10. darkgen

    Wise Guy

    Joined: Mar 8, 2005

    Posts: 2,177

    Location: London, UK

    HAH, my edit! :)
     
  11. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 8,997

    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.
     
  12. tom_e

    Man of Honour

    Joined: Dec 26, 2003

    Posts: 25,552

    Location: West mids

    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
     
  13. darkgen

    Wise Guy

    Joined: Mar 8, 2005

    Posts: 2,177

    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.
     
  14. kkelly

    Mobster

    Joined: Oct 8, 2003

    Posts: 2,583

    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 :|