|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/29/2009 12:54:23 PM
Posts: 6,
Visits: 12
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/29/2009 12:54:23 PM
Posts: 6,
Visits: 12
|
|
I figured it out.
select cast(substr(datetime,1,11) as date format 'ddbmmmbyyyy'),
cast(substr(datetime,13,8) as time(0))
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 4:40:02 PM
Posts: 717,
Visits: 466
|
|
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
|
|
|
|