Issue with Casting string to date in Teradata

Question

I have a string column - COL1 in TABLE1 which is if string data type. This table is loaded by Informatica session ( data coming from mainframe) and the format of the COL1 is YYYY-MM-DD. Now I have to use TABLE1 as the source in my next mapping . In the SQL override query of second mapping i will be casting COL1 to date using the below query .

SELECT
CAST(COL1 AS DATE FORMAT 'YYYY-MM-DD') AS CHK_DT FROM TABLE1

But when i try to execute this query in Teradata SQLA, just to check if it runs fine it gives me below error.

SELECT Failed. 2666:  Invalid date supplied for COL1.

Can you please help me resolve this issue ? This is not the only date column which has issue, there are two more date columns . I guess the resolution is same for all three columns .

P.S - Just to verify, I updated all rows of COL1 of TABLE1 as 2016-12-12 and ran the select statement, select worked fine . I then updated COL1 of all rows as 2016-13-12, it gave same error . If either of DD or MM is more than 12, it is giving me error

Thanks

Answer 1

If DATE is represented/stored in ANSI standard literal YYYY-MM-DD, the CAST will work.

SELECT CAST('2016-12-13' AS DATE FORMAT 'YYYY-MM-DD') AS Date1

However i doubt that in your case.

The date is most probably in YYYY-DD-MM format. In that case the ANSI standard format will throw the error. You need YYYY-DD-MM

select  CAST('2016-13-12' AS DATE FORMAT 'YYYY-DD-MM') AS Date2

P.S. You can confirm the conversion to date using TYPE() function. It should return DATE in your case

Answer 2

Hi Please try this piece of code

CAST(CAST(date_col AS FORMAT 'YYYY-MM-DD') AS VARCHAR(15))

instead of the transformation you are using.

Answer 3

Thanks for your response. However the issue was something else. Some of the incoming records had space in this column . So I had to tweak my informatica mapping to put a trim on date column . Now the select is running fine . Thanks for your time .