Teradata data type conversion and ANSI data type conversion

Database

Teradata data type conversion and ANSI data type conversion

 

Hi,

Teradata 13.0 when using ANSI teradata mode data conversion decimal to varchar of same length getting blank as output.

sel 0 (decimal(3,0))(varchar(3))

0

--------

 

But using ansi mode conversion format getting expected value as 0.

sel cast(cast( 0 as decimal(3,0)) as varchar(3))

0

---------

0.

Is this a bug in teradata data conversion? I was expecting same result in both the conversions.

1 REPLY
Senior Apprentice

Re: Teradata data type conversion and ANSI data type conversion

This is independent of the session mode ANSI/Teradata.

0 (decimal(3,0))(varchar(3)) is a Teradata style typecast, while cast(cast( 0 as decimal(3,0)) as varchar(3)) is ANSI style.

When you do a sel FORMAT(0 (decimal(3,0))) you'll get ---9., i.e. three digits right aligned with leading spaces and a leading sign. Teradata style typecasts use this format resulting in 5 characters "   0." (three spaces+0.) and then you strip of the last two characters due to the VARCHAR(3) :-)

ANSI style always typecasts left aligned.

For a DECIMAL(3,0) you need at least 4 characters or change the FORMAT to 'ZZ9'