Oracle Analytics Cloud and Server

Products Banner

Oracle DV Desktop - How to get days difference between 2 date columns

Received Response
114
Views
7
Comments

Content

Hi, Wondering if anyone can assist.

I have 2 columns with dates in them but I can't calculate the difference of days between them in a new column.  I have tried a number of function and playing around with getting it right, but can't crack it.  I have tried the below but while it validates ok it doesn't return the days.

CURRENT_DATE - CAST( Actual Date as TIMESTAMP)

I have also tried using: TIMESTAMPDIFF(interval, first_date, second_date)

Tagged:

Answers

  • Hello Egon

    This 1mn 45s  video shows how to use the timestampdiff syntax in Oracle DV, can you confirm this is not working for you ?  https://youtu.be/7NeAjGu2dVE

    Thanks

  • Hi Philippe,
    Thanks for sharing the video!  I have tried this but it is not working.

    I have tried first without casting but the calculation does not validate.

     

    I then tried with casting.  It validates ok and then when I try to add to the canvas it throws an error?.

    error1.png

  • The error seem to indicate that your data may not have the right format. Any chances you can share a smal subset of your data (just the dates field and one column for exp) ?

  • Yes I thought that might be the case but I can't see any issues.  Just a normal .csv from Excel...

    Error4.png

  • Ah!  The data type is not a date field - looking at the video again that's the only difference.  It get's back to the other post I had - about not being able to convert the field to a date as the application can't handle a null or blank field when I try to use the "Convert to Date..." menu option.

    Any idea on that one as it seems  a pretty standard thing to have some blank fields.

  • Phillipe was correct - it was a data quality issue.  Once I changed the CSV data to have all dates as dd-MMM-yy format, I am able to convert the field to a date field without issue.

    Somewhat surprising the Oracle DV app doesn't support this no doubt very common data convert issue, ie automatically from d-MMM-yy to dd-MMM-yy

     

  • Phillipe was correct - it was a data quality issue.

    9 times out of 10 it is :-)

    Somewhat surprising the Oracle DV app doesn't support this no doubt very common data convert issue, ie automatically from d-MMM-yy to dd-MMM-yy

    Date format masks is one of the most common error sources; no matter what you're doing and what technology you are using. And heeding that DVD analyzes just about any and every data source there is, with - as a result- literally any data interpretation conceivable it becomes clear why precision is required and why the tool can't do everything automatically or from the get-go.