Oracle Analytics Date Problem of Data Sets

dilek

Hi,

I am a newbie on OAS 6.4. I created a data set from an sql connecting database and gets the data from that way. However, I aware that the results of sql (only date columns) on data visualizer not same as results of Toad (application we use as an sql editör) On data visualizer, date columns shows 1 day after.

For example;

Correct value: 31/08/2021

Dv Result: 01/09/2021

Do you have any idea about this issue? If you have, please help me.

Regards,

Dilek

Gianni Ceresa

Hi,

Isn't it a timezone thing?

What you see as date is maybe a full date & time field, and DV does apply some timezone offset to translate it into your own timezone. If you were to see the time part of the field, it would be more obvious, but when seeing only the date you maybe see it 1 day later before it did add a bunch of hours.

Dates & time should be managed in your query as much as possible, making sure to retrieve only a date if you need that and not a date & time.

dilek

Thank you for your answer @Gianni Ceresa

I aware that, when I edit a column comes from sql statement on dv, it automatically changes the date value. Editing means can be followings;

• adding a function (like cast)
• renaming column name (for ex period -> my_period)

When I remove these editings, date values are corrected.

Thank you again.

Have a good day.

[Deleted User]

It all comes down to: What exactly is stored in that column in the table? 31/08/2021 00:00:00? And what is the exact physical data type in the table?

Then the second thing is: What is the data type in DV? Are you using a normal data set or are you doing any transformations/using a data flow/etc?

As Gianni indicated there is logic happening in DV and it will try to convert things according to timezone settings.

Gianni Ceresa

If your source column has a time component, in your SQL query do a TRUNC() on it. It will remove the time component and DV will maybe stop doing timezone manipulatons on something not having a time.