I have a field time_key (bigint) that shows '20160817'
I want to format it so that it displays as '08172016' in the sql.
I've played around with trim,cast,substr, can't seem to get it to work.
Any help is much appreciated.
I resolved this one:
TO_CHAR(cast(cast(cast(table.column as CHAR(8) ) as DATE FORMAT 'YYYYMMDD') as DATE FORMAT'MMDDYYYY'),'MMDDYYYY') AS converted
The DATE FORMAT approach is probably better, but since you mentioned substring, for comparison, the substring+concatenation approach would look like the following:
select substring(cast(time_key as varchar(8)) from 5 for 4) || substring(cast(time_key as varchar(8)) from 1 for 4) from ...