Invalid date supplied in teradata--varchar date to timestamp(0)

Database
Enthusiast

Invalid date supplied in teradata--varchar date to timestamp(0)

have a date column in source which is defined as varchar(255). I want to convert that to timestamp(0). I am using the below query:

SEL CAST(CAST( SERIND AS CHAR(10)) || ' 00:00:00' AS TIMESTAMP(0))
FROM DP_BOX.SOC1
WHERE SERIND ='20130518'

I am getting invalid timestamp here. Sample SERIND are

20170509
00000000

Can anyone please help me on this?

 

1 REPLY
Teradata Employee

Re: Invalid date supplied in teradata--varchar date to timestamp(0)

try:

cast(cast(serind as date format'y4mmdd') as timestamp)

or:

cast(cast(cast(serind as char(8)) as date format'y4mmdd') as timestamp)

 

However, '00000000' will not work as a date, so you will need to enclose this in a CASE statement that makes zero a NULL or a default date.