Cast and format

UDA
Fan

Cast and format

Hi !

First, i want to apologize for my english. I'm french and my english is very bad.

I have a problem with an extract with MLoad.

When i try to converte a VARCHAR like '70,37' in DECIAML(6,2) with :

CAST('70,37' as DECIMAL(6,2) format 'ZZZZ,ZZ')

Teradata return 7037,00 not 70,37

The probleme is this character ',' in the data. With a dot it's ok. But i can't change the data ...

is existe a format to chage that ?

Thanks a lot for your help !

14 REPLIES
Senior Apprentice

Re: Cast and format

Hi Seneg,

don't apologize, I'm german and my french is worse than your english :-)

The format doesn't help because the comma is used as a thousand separator.

If you're on TD14 you can switch to Oracle's TO_NUMBER which is a bit more flexible:

CAST(TO_NUMBER(col, '9999D99','NLS_NUMERIC_CHARACTERS = '',.''') AS DECIMAL(6,2))

Otherwise if there's always two fractional digits you can simply CAST(col AS DECIMAL(8.0)) / 100.00

Fan

Re: Cast and format

Hi ,

Thank you for your help, but this doesn't work.

We are in TD13 and i think TO_NUMBER() is not in.

Have you a solution for TD13 ?

Senior Apprentice

Re: Cast and format

As I wrote, if there's always the same number of fractional digits with the string you can do 

CAST(col AS DECIMAL(8.0)) / 100.00 -- 1000.000 for 3 digits, etc.

If you're on TD13 you still might have the oTranslate or oReplace UDF:

CAST(oTranslate(col, ',', '.') AS DECIMAL(6.2))

Or brute force:

CAST(     SUBSTRING(col FROM 1 FOR POSITION(',' IN x) - 1) 
|| '.' || SUBSTRING(col FROM POSITION(',' IN x) + 1)) AS DEC(6,2))
Fan

Re: Cast and format

I have choose this solution :

CAST(CASE WHEN position(',' IN :COL02) = 0 
THEN :COL02
ELSE substr(:COL02,1,position(',' IN :COL02)-1)||'.'||substr(:COL02,position(',' IN :COL02)+1)
END
as DECIMAL(6,2) format 'ZZZZZZ,ZZ')

Because if there are a value without decimal, Teradata return a error.

Thanks for your help, i close the discution.

Enthusiast

Re: Cast and format

hi All,

i would like to face a new topic about date fields subtraction in TERADATA 12.0 environment;

briefly i have two field containig data, the first one defined as "data" and the second one as "varchar(8)".

executing my query as follow :

SELECT top 100

CLI.A40_CLNT_LCL_ID

, CLI.DT_RFRM

, CLI.A36_COUNT_RTN_DT

, cast(cast(CLI.A36_COUNT_RTN_DT as date format 'yyyymmdd') as date format 'yyyy-mm-dd')

, (CLI.DT_RFRM - substr (CLI.A36_COUNT_RTN_DT,2,8)) as diff_date

FROM FEED_BMRC.CAPRI_CLIENT CLI

the result i got in diff_data field is like :

A40_CLNT_LCL_ID   DT_RFRM       A36_COUNT_RTN_DT    cast date         diff_date

301260541              31/12/2033   20131210                     10/12/2013     1200021,00

instead of the number vof day between dtes.

someone's may help me in this problem?

thank's a lot for your attention and courtesy in answering

K Regards

Pio

Senior Apprentice

Re: Cast and format

Hi Pio,

what do you expect?

A DATE minus a DATE results the number of days difference,but you substract a string from a date:

DATE '2033-12-31' - '0131210'

Because this is not defined for dates and because minus is a numeric operator both operands are automatically casted to a float and the substracted.

, cast(cast(CLI.A36_COUNT_RTN_DT as date format 'yyyymmdd') as date format 'yyyy-mm-dd') as datecol
, CLI.DT_RFRM - datecol as diff_date

Btw, of course a DATE should not be stored as a string (and not as a VARCHAR instead of CHAR(8))

Enthusiast

Re: Cast and format

Hi Dieter

thank's, thank's , thank's a LOT

I am astonished for my incompetency.

K Regards

Teradata Employee

Re: Cast and format

The Teradata Database has i18n formatting and parsing capabilities going back to V2R5. Start by defining your French Locale as an SDF with the TDLoacleDef utility. See the reference manuals for more details.

Enthusiast

Re: Cast and format

hi All,

i came back on topic about date fields conversion in TERADATA 12.0 environment;

briefly i have primary to convert a char(10) "NER.DT_FNE_VLD_RAT"  field containing a data like 26.01.2013 but receive an error message " precision error in float type constant or during implicit conversions"  executing my query as follow :

SELECT

CLI.A40_CLNT_LCL_ID, CLI.A4_CORP_NAME, CLI.CD_SAE, CLI.CD_RAE, CLI.MERC, CLI.COD_MDLL

, CLI.COD_MOTIVAZ, CLI.DT_RFRM,

cast(cast(NER.DT_FNE_VLD_RAT as date format 'yyyymmdd') as date format 'yyyy-mm-dd') as Data_Scad_Rating

, cast(cast(NER.DT_INI_VLD_RAT as date format 'yyyymmdd') as date format 'yyyy-mm-dd') as Data_Val_Rating

, CLI.DT_RFRM - Data_Val_Rating as Vetusta_Rat

, NER.DESCR_RAT

, NER.CLASSE_RAT

FROM

FEED_BMRC.CAPRI_CLIENT CLI, DXDWH_DB.DWH_FD FID, DXDWH_STG.NERATING NER

someone's may help me in this problem?

thank's a lot for your attention and courtesy in answering

K Regards

Pio