I have the below issue:
Column in table should be a date, but show as :
I want to convert these so they look more like a date i.e.
I have tried:
sel cast (column_x AS DATE Format 'YYYYMMDD')
but doesn't seem to work. - Any help Appreciated.
Solved! Go to Solution.
Is this your actual data, YYYYM[M]DD? Ouch.
2017331 -> 2017-03-31
20171231 -> 2017-12-31
Hopefully not YYYYM[M]D[D]
201733 -> 2017-03-03?
2017111 -> 2017-????
The examples that you've provided are invalid dates.
Instead of 2017331 do you mean 20170331?
If so then simply use:
SELECT CAST(column-name-19000000 AS DATE) FROM table-name;
This will change data type to a DATE, then you can format it as required.
If the data value that you've provided is what is stored then you have to manipulate it a bit. The following has the logic which you should be able to simplify:
SELECT column-name MOD 1000 AS c2 ,10 * (col1 - c2) AS c3 ,CAST( ((c3+c2) - 19000000) AS DATE) AS date_col1 FROM table-name;
The later is the correct value i see. (2,017,331).
***EDIT: - I have a feeling this is worse than i anticipated - i can see data with 2,016,342 - wouldnt even know what that is even implying :| !
However, after playing around and tweaking the suggested logic, the outcome i get:
SELECT Failed. 2666: Invalid date supplied for table_name.column_name
Check max(columnname mod 1000), when it's 365 or 366 you got the year plus the day of the year:
Check max(columnname mod 1000), when it's 365 or 366 you got the year plus the day of the year:To_Date(trim(columnname), 'yyyyddd')
Thanks for the replies.
Checking MAX(column_name mod 1000) = 331