Archives of the TeradataForum

Message Posted: Wed, 27 Feb 2008 @ 14:28:25 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

This is simply converting an integer in DDMMYY form to a formatted string.

As Michael said, we need to know the data type of DELNMAST_PREVD6. I'll assume it is an integer and offer two approaches. Approach 1 is closer to the original Oracle code; approach 2 is easier and adequate for this particular question.

Approach 1: Convert to a true Teradata date and format. From the inside out...

DECODE(DELNMAST_PREVD6,0,010100,DELNMAST_PREVD6)  [e1]

This changes 0 values in DELNMAST_PREVD6 to 010100 and leaves other values unchanged. In Teradata:

     case when DELNMAST_PREVD6 = 0 then 010100 else DELNMAST_PREVD6 end

LPAD(e1, 6,0)  [e2]

This left-justifies the integer in a 6-byte character string.

TO_DATE(e2, 'DDMMYY')  [e3]

This converts the character string to a date, assuming that it is in DDMMYY form. In Teradata, you could do these two steps, but it is better to avoid conversions from numeric to character and back again. And what century is it? The following all-numeric operation assumes the 21st century and converts the DDMMYY integer to an integer that looks like a Teradata date:

(((e1 MOD 100) + 100) * 10000) + ((e1 / 100 MOD 100) * 100) + (e1 /
10000)  [e4]

If your dates are in the 20th century, omit the "+ 100". If they straddle both centuries, a more complex expression will be needed.

TO_CHAR(e4, 'YYYY-MM-DD HH24:MI:SS')

This converts the date to a formatted character string. This works in Oracle, since all "dates" are actually timestamps. In Teradata, however, they are different data types, so you have to convert the integer (e4) to a date, then format it, and finally add ' 00:00:00' at the end.

(e4 (date, format 'YYYY-MM-DD') (char(10)))) || ' 00:00:00'

Here's the whole thing:

     *select ... *
     **
     *case* *when* DELNMAST_PREVD6 = 0 *then* 010100 *else* DELNMAST_PREVD6
     *end* *as* e1,
     ((((e1 MOD 100) + 100) * 10000) + ((e1 / 100 MOD 100) * 100) + (e1 /
     10000) (date, format 'YYYY-MM-DD') (char(10))) || ' 00:00:00'

Approach 2: Convert the integer to a character string right away and just manipulate that.

You still need the case expression. Convert that to a character string and move the bytes around:

     select ...
     *cast*(*cast*(*case* *when* DELNMAST_PREVD6 = 0 *then* 010100 *else*
     DELNMAST_PREVD6 *end* *as* *for*mat '9(6)') *as* char(6)) e2,
     '20'||substr(e2, 5,2)||substr(e2, 3,2)||substr(e2, 1,2)||' 00:00:00'

	<Prev	Next>		<<First	<Prev	Next>	Last>>