All Forums UDA
sinara 7 posts Joined 10/15
09 Feb 2016
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

dnoeth 4628 posts Joined 11/04
09 Feb 2016

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.

Dieter

sinara 7 posts Joined 10/15
09 Feb 2016

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

dnoeth 4628 posts Joined 11/04
09 Feb 2016

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

 

Dieter

You must sign in to leave a comment.