moving first 4 characters to last 4 characters

Database

moving first 4 characters to last 4 characters

Hello,

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.

Eddie

2 REPLIES

Re: moving first 4 characters to last 4 characters

I resolved this one:

select

TO_CHAR(cast(cast(cast(table.column as CHAR(8) ) as DATE FORMAT 'YYYYMMDD')  as DATE FORMAT'MMDDYYYY'),'MMDDYYYY') AS converted

Teradata Employee

Re: moving first 4 characters to last 4 characters

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