CODEWITHSUNDEEP
teradata
Naveen
Naveen

Reputation: 701

Convert varchar(50) to Date Format 'yyyy-mm-dd' in teradata?

I have a column defined as Date format yyyy-mm-dd while creating.

I want to insert data from other table which has that column as varchar(50)

While selecting and inserting into the table I got this error

INSERT Failed. 2665: Invalid date.

Can someone help me in casting this?

INSERT INTO TEMP_TABLES.FACT
(

CUSTOMER_ACCOUNT_ID, 
LOB_START_DATE, 

)

SEL  
CUSTOMER_ACCOUNT_ID,
I.start_date   as LOB_START_DATE,

FROM  #LOGIN I

left join JOURNEY_TABLE.DOTCOM_DIM d1
on I.PAGES = d1.PAGE_DESC

This is the example of date stored in varchar(50) field : 2014-04-03

Thanks in advance

Upvotes: 0

Views: 42581

Answers (3)

Rob Paller
Rob Paller

Reputation: 7786

In more recent versions of Teradata there is a TRYCAST() function. This function will attempt to cast the data and return NULL if the conversion fails, instead of failing the statement.

INSERT INTO TEMP_TABLES.FACT
(
CUSTOMER_ACCOUNT_ID, 
LOB_START_DATE, 
)
SELECT Customer_Account_ID
     , TRYCAST(I.Start_Date AS DATE) AS LOB_START_DATE
FROM  #LOGIN I
LEFT JOIN JOURNEY_TABLE.DOTCOM_DIM d1
  ON I.Pages = d1.Page_Desc;

Upvotes: 0

Beth
Beth

Reputation: 9617

I think all you need is the format statement

examples here

SEL  
CUSTOMER_ACCOUNT_ID,
cast(((I.start_date (date, format 'yyyy-mm-dd'))(char(10))) as LOB_START_DATE,

Upvotes: 2

Andrew
Andrew

Reputation: 8758

In case it would be helpful, here's a query that should allow you to identify the rows with invalid dates:

select
*
from
#login t1
left outer join sys_calendar.calendar t2
on t1.start_date =  cast (cast(t2.calendar_date as date format 'YYYY-MM-DD') as char(10))
where t2.calendar_date is null

Any rows that return from this query will have invalid dates.

Upvotes: 2

Related Questions