2620. The format or data contains a bad character

Database
N/A

2620. The format or data contains a bad character

Hello folks, 

 

I have below code which is used as part of a WHERE clause and its causing the 2620 error. 

 

Expect the variables used to have values like:

LOCAL_DATE_KEY='2016-12-31' 

WK_NBR_IN_YEAR=23 

 

SyntaxEditor Code Snippet

    CAST((EXTRACT(YEAR FROM VMLA.LOCAL_DATE_KEY)||DD.WK_NBR_IN_YEAR) AS INT)=CAST('2016'||'23'AS INT)

 Please suggest what is the cause for 2620 error and how to fix it. 

 

Regards,

Sri.

2 REPLIES
Teradata Employee

Re: 2620. The format or data contains a bad character

It looks like local_date_key is char. Extract operates on a date data type. It will be necessary to cast the char to date to use extract. Alternatively substr could be used.

To make the comparison work, it will also be necessary to format the currently implicit cast of wk_nbr to be two digits rather than the default format for the numeric type of the field.
N/A

Re: 2620. The format or data contains a bad character

Look at the result of Extract(YEAR From LOCAL_DATE_KEY)||WK_NBR_IN_YEAR, it's not '201623' but '       2016  23' (due to automatics typecasts applying a Format with leading spaces).

 

Cast((Trim(Extract(YEAR From LOCAL_DATE_KEY))||Trim(WK_NBR_IN_YEAR)) AS INT)

or better avoid cast to string and back to int using

Extract(YEAR From LOCAL_DATE_KEY) * 100 + WK_NBR_IN_YEAR