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

EgonM-Oracle

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)

Philippe Lions-Oracle

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

EgonM-Oracle

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?.

Philippe Lions-Oracle

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) ?

EgonM-Oracle

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

EgonM-Oracle

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.

EgonM-Oracle

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

 

[Deleted User]

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.