Hi all,
I'm looking for help converting a column to date from text, I tried looking at posts and the following documentation that appears to be missing a step behind converting to date.
Context:
Problem:
The column "LAST_UPDATE_DATE" is reading date data in a text format. I am performing the following steps to attempt to convert this to a date form:
I can't find any Oracle documentation on this wizard or how to use it. The output I'm trying to achieve is the following format:
Inputting the following leads to errors
At this point I'll settle for just having a short date (e.g. "6/7/22") but I can't seem to figure this out either.
Can anyone help?
Ok, I had to give it a try to find out what was going on.
I call it a bug: the function exists, the formula is valid, but still it doesn't work.
The workaround is to use a TIMESTAMP instead of a date:
TIMESTAMPADD(SQL_TSI_DAY, "LAST_UPDATE_DATE", TIMESTAMP '1900-01-01 00:00:00')
If you are motivated to raise an SR to report the bug when entering the version with DATE '1900-01-01' feel free to do it :D
If your source is a CSV file, there is not an issue, it works as it should.
CSV doesn't have data types, it's text or number. You can tell Excel to convert a number to a date, but it was (and still is) a number and it's Excel doing the conversion. Being a number, you can't turn it into a date just by trying a cast or a format, you first need to calculate the date from that number.
In Excel dates are actually numbers, just displayed as dates "visually": it's a number representing the number of days from 01/01/1900 (if not wrong).
In this case you could get real dates by using TIMESTAMPADD, and adding that number of days to 01/01/1900.
Something like this could work: TIMESTAMPADD(sql_tsi_day, "LAST_UPDATE_DATE", DATE '1900-01-01')
And if your LAST_UPDATE_DATE is not an integer, the fractional part is the fraction of day representing the time.
@Gianni Ceresa Thank you for your response!
Great point about how data is stored in a Csv.
I think ultimately what I'm looking for is the OAC equivalent of that visual change to display the stored number as a date. This data will be used to provide visuals aggregated by date, so I am concerned about these visuals showing the numerical value of the date, as opposed to the date itself.
I tried the formula you suggested and retrieved this error:
In an attempt to troubleshoot this error, I played around with the data type and changed it from text to number, and vice versa to see if the formula would work, however it gave the same error every time.
You should write DATE '1900-01-01' , the word "DATE" is important because it's a casting of the text following as a date. As an alternative you can do an explicit casting with the CAST function or use the TO_DATETIME function to generate a date.
And technically there can't be the equivalent to Excel with dates as numbers, mostly because Excel does its own cooking. The idea that a date is the number of days from 01-01-1900 is a very Excel thing. The closest "standard" would be the epoch time: the number of seconds from 01-01-1970. But even then it is still not a date, it's a number.
And that's why OAC has a number of functions to convert text or numbers into real dates (because data types do matter!).
This forum has a very poor style, one could expect a different background for "code", but I can't control the style of the forum to make the code pasted more visible.
TIMESTAMPADD(sql_tsi_day, "LAST_UPDATE_DATE", DATE '1900-01-01')
I see, so it appears there is no visual conversion I can do for the date, at least here in the data editor, and specifically for .csv file types.
I rewrote that formula and this time received no error! However, it then made every value in my date column null:
Going back to my point about the dates showing as numerical values in Visuals, I went and created a very simple line chart to see if there was a way I could change the date's display to no avail (take a look at the x-axis)
Any ideas on what else I could try?
I would say the issue could be a circular reference: you are redefining the formula for "LAST_UPDATE_DATE" to be a formula referencing "LAST_UPDATE_DATE".
Add a new column with the formula, you can keep your "LAST_UPDATE_DATE" as a number if you want, but it's pointless (so you can hide it), and then you have a new column (name it what you want, but not "LAST_UPDATE_DATE") with the real dates.
And again: stop wasting your time trying to display dates from your numbers directly. It's impossible, because that number isn't a standard way to represent a date anywhere else than Excel. It can't be done just with a click as a display transformation.
@Gianni Ceresa Thank you for your consistent support.
I tried re-writing your above formula as a new column (as opposed to a transformation) and received the same null results:
Let me know if there is anything else you think I can try. I feel if we can find the answer to this it will be very helpful to future analysts using OAC.
@Gianni Ceresa You are the man! Thank you for helping me figure this out, I posted your response as the best answer. I will definitely be looking into a bug report.
Thanks again!
Glad it works @Allen Braun, and thanks for accepting the answer! That really helps out the community and we appreciate it :).
Hi @Allen Braun Looks like @Gianni Ceresa provided a way to solve your issue - in the future, these two blogs may also be helpful with some complex text columns that need to be converted to dates.
If you have a sample file that you can share with me here or privately - I can take a look and see why our semantic profiler did not recognize it as a date. My contact info is in my profile. thanks.
