convert char timestamp to TD timestamp

Database
Enthusiast

convert char timestamp to TD timestamp

I have to convert this character timestamp to be able to extract date parts and or time parts for calculations. The fields look like this. I have tried casting the field in various ways and nothing seem to work. The fact that the field has milliseconds seems to be the issue...maybe.

14 Sep 2009 10:09:54:300
14 Sep 2009 12:29:38:100
14 Sep 2009 09:10:44:100
14 Sep 2009 10:59:06:500
14 Sep 2009 11:17:44:400
14 Sep 2009 18:04:00:000
14 Sep 2009 18:42:06:000
2 REPLIES
Enthusiast

Re: convert char timestamp to TD timestamp

I figured it out.

select cast(substr(datetime,1,11) as date format 'ddbmmmbyyyy'),
cast(substr(datetime,13,8) as time(0))
Junior Contributor

Re: convert char timestamp to TD timestamp

It's not the milliseconds, it's the colon which separates them, this must be a period:

SELECT '14 Sep 2009 10:09:54:300' AS x,
SUBSTRING(x FROM 1 FOR 20) || '.' || SUBSTRING(x FROM 22)
(TIMESTAMP(3), FORMAT 'ddbmmmbyyyybhh:mi:ss.s(3)')

Dieter