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?
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.
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.
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))