Converting a string to Timestamp and checking if it has valid timestamp

UDA
Enthusiast

Converting a string to Timestamp and checking if it has valid timestamp

Hi ,

  I am trying to write a select statement for converting a string to timestamp(6) and if string value  is missing or invalid timestamp , then it should have a default value as '0001-01-01 00:00:00:000000' . source field type is varchar(50). 

select case when src_fld is NULL or ??? then '0001-01-01 00:00:00:000000' else cast (src_fld as timestap(6) as format 'yyyy-mm-ddBHI:MM:SS') end from src_table

Is it in correct format?Not sure how to check a gien string as valid timestamp,hence kept it as '???' Can anyone please help me?

Thanks

3 REPLIES
Junior Contributor

Re: Converting a string to Timestamp and checking if it has valid timestamp

There's no built-in function to check if a string holds a valid timestamp, you might need to create a C-UDF or a really complicated SQL-UDF.

Enthusiast

Re: Converting a string to Timestamp and checking if it has valid timestamp

Thanks dnoeth for your reply. Is there any way to check if casting did not happen correctly can we set the field to a default value?

ex:below query will return invalid timestamp and when we get this error can we set the field to default one

 cast ('abcd' as timestap(6) as format 'yyyy-mm-ddBHI:MM:SS') 

Sorry if i am asking a silly question. I am just in learning stage.

Thanks

Junior Contributor

Re: Converting a string to Timestamp and checking if it has valid timestamp

In TD15.10 there's a new TRYCAST function, which returns NULL for invalid data, but it doesn't accept a FORMAT:

TRYCAST(col AS TIMESTAMP(6))