All Forums Database
vinodhteradata 1 post Joined 02/12
17 Oct 2014
Teradata data type conversion and ANSI data type conversion

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))
But using ansi mode conversion format getting expected value as 0.
sel cast(cast( 0 as decimal(3,0)) as varchar(3))
Is this a bug in teradata data conversion? I was expecting same result in both the conversions.

dnoeth 4628 posts Joined 11/04
18 Oct 2014

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'


You must sign in to leave a comment.