Converting to Date in Oracle DV Data Editor (Missing Documentation?)

Gianni Ceresa

In this case, there is nothing that can be done on "text to date" conversion, because the data is not a date at all.

This is an example of the data: the first column is the "date".

In Excel, those numbers can be turned into dates visually, just telling Excel that the column is supposed to be formatted as a date.

But nowhere else it will be a date, because it isn't a standard like the Unix time or things like that. It's a pure Excel thing. And when you load an Excel file you could see it as a date because Excel has formats, but in a CSV it's just a number.

Luis E. Rivas -Oracle

Thanks @Gianni Ceresa , the details provided may help us automate the conversion in the future.

Allen Braun

@Luis E. Rivas -Oracle Your blogs are fantastic! Part two of your blog is more applicable to this specific dataset, but I couldn't get a date/time format to work using the included Date Converter. I do have a sample dataset I can send you - message me to let me know what's the best way to give it to you.

Ambient

Just one note of caution (I don't have access to validate this right now) wrt the proposed solution:

TIMESTAMPADD(SQL_TSI_DAY, "LAST_UPDATE_DATE", TIMESTAMP '1900-01-01 00:00:00')

Excel converts '1900-01-01 00:00:00' to a value of 1. In fact, Excel formats the value of 0 to the imaginary 1/0/1900, not 12/31/1899.

You may want to validate whether your converted dates are off by a day. If so, you will have to change your anchor day:

TIMESTAMPADD(SQL_TSI_DAY, "LAST_UPDATE_DATE", TIMESTAMP '1899-12-31 00:00:00')

Gianni Ceresa

@Ambient , possible!

It's one of those Excel things that would deserve a test (or to search to confirm it), I just remember it is from 1900, just to annoy everybody having to handle it :D

But OP should easily know if his dates are off by 1 day or not.

[Deleted User]

Yes, Excel is "special" when it comes to these things and can't do things normally. Luckily DV correct for this where basic dates are concerned:

Problems re-appear when you do time arithmetic as you can imagine.

Never trust Excel, kids! ;)

Allen Braun

@Christian Berg and @Ambient both great points! I went ahead and referenced the source data and did some spot checking to make sure the dates lined up. As it turns out the dates I converted using the following formula was off by two days!

TIMESTAMPADD(SQL_TSI_DAY, "LAST_UPDATE_DATE", TIMESTAMP '1900-01-01 00:00:00')

To accomdate this I added a - 2 to the end of the formula which corrected the dates.

TIMESTAMPADD(SQL_TSI_DAY, "LAST_UPDATE_DATE", TIMESTAMP '1900-01-01 00:00:00') - 2

Ambient

The fact that the difference was 2 didn't feel right to me at first, but then I remembered a project that I was on in the mid-aughts. At the time we found out that Excel had purposely perpetuated a bug from the Lotus 123 days to preserve data integrity, by including Feb. 29, 1900, even though 1900 was not a leap year.

https://en.m.wikipedia.org/wiki/Leap_year_problem

https://learn.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year

one of those little bits of trivia…

Gianni Ceresa

And that's why everybody love Excel : it comes with its own logic, but because of the number of users, it is always right 😁

Not like databases or any other type of files where data types exists and are a real thing 🙄