All Forums Database
efgeorge18 2 posts Joined 08/16
29 Aug 2016
moving first 4 characters to last 4 characters

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.

efgeorge18 2 posts Joined 08/16
29 Aug 2016

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

tomnolan 594 posts Joined 01/08
29 Aug 2016

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 ...

You must sign in to leave a comment.