Why does extract day change the date?

User_VMJII

Why does extract day change the date (like in first row from 03/29/2023 to 03/28/2023)? Shouldn't it stay the same?

I also tried that:

But in this case all the values ​​disappear.

I think I have the correct data type:

What can I do to keep the date as it is, except to delete the timestamp (12:00:00.000 AM)?

Gianni Ceresa

ExtractDateignore the time component and set it to 12:00:00 am (midnight), therefore doesn't really matter what was the time of your dates, and if you add an hours or 2 in there before calling the function…

Because your data comes from a database, what data type is it there? (and what database?)

Is it possible that it has a time zone component? OAC/OAS doesn't seem to works well with time zones in date time values, it actually doesn't support time zone in most functions or manipulation of data.

If you want to be fully sure about the data you get, can't you manipulate it as required in the database? Either by using a SQL query to get the data (doing your manipulations), or by using a view containing the logic?

Joel

It might be worth checking the actual values in the data source to ensure they’re correct. EXTRACTDAY returns the timestamp for midnight on the day of the actual date time value.

This might be a bit primitive but it should do the job:

CAST(date_column AS date)

Gianni Ceresa

The change of date is probably a timezone effect: midnight in your timezone (probably GMT+1 or more) translated to UTC means a day earlier at 11pm or earlier (depending on your timezone).

User_VMJII

@Joel unfortunately, the cast function also calculates the same (-1 day). Apparently it's a timezone, as @Gianni Ceresa mentioned (GMT +1). Maybe you have another suggestion? I tried with extractday + 1 but it doesn't work, and I also don't think it's the most suitable way, because in the case of a different clock it would calculate incorrectly. Is there any function that would easily cut off the last x characters (like a substring but for non text)?

Gianni Ceresa

What is your data source behind that dataset? Is it a database or a file or something else?

I imagine you could workaround it in a "dirty" why by first adding some hours (a few more than your time zone, to safely handle daylight saving time changes etc.) to your date and then get the day from it. The time would be totally wrong, but as long as you only care about the date itself, it will just work

User_VMJII

My data source is database. I am not the most in favor of the proposal. If I add 1 hour to all, I save these with 12:00 and "spoil" those with 11:00, am I wrong?

Mostafa Morsy-Oracle

@User_VMJII

in your Database what is the TimeZone you can change same time Zone in OAC/OAS System Settings

Or change "Default Time one for Date Calculations" to get correct value when extractDay function used

"Default Time one for Date Calculations" can be found under system settings

Joel

You'll need to resolve the timezone issue in your data source and probably it's best to do all manipulation of the date formatting in your database so that all you do in OAC is pull back the formatted date column directly from the database.

Mostafa Morsy-Oracle

@User_VMJII

Please check ☝️

User_VMJII

@Gianni Ceresa it works, I added 6 hours. But it is not clear to me why there are deviations in the new date (4:00/5:00)? —edit: I found out why - because of summer and winter time (+1/+2 GMT) :)

@Mostafa Morsy-Oracle I would also like to try this ("Default Time one for Date Calculations") because it is a more elegant way, but I can't find where System Settings are located