nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

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)

Find more posts tagged with

Tips & Tricks

Analysis

Accepted answers

All comments

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.