CODEWITHSUNDEEP
sqldatabasedb2
Lilantha Lakmal
Lilantha Lakmal

Reputation: 33

How to convert VARCHAR to TIMESTAMP in DB2

I have a varchar value in BD2 Table like below format

121226145503+0530

I want to convert this varchar value to time stamp format like below

2012/12/26 14:55:03

Upvotes: 2

Views: 32982

Answers (2)

CRPence
CRPence

Reputation: 1259

with d (cwd) as                                              
 (values( cast( '121226145503+0530' as varchar(20))))        
select cwd as vc_input                                       
 ,  to_date(cwd, 'YYMMDDHH24MISS'      )        as ts_from_vc
 ,  varchar_format  ( to_date(cwd, 'YYMMDDHH24MISS')         
                    , 'YYYY/MM/DD HH24:MI:SS' ) as vc_from_ts
from d                                                       
; -- output from above query follows, as likeness of a report:
....+....1....+....2....+....3....+....4....+....5....+....6....+....
VC_INPUT              TS_FROM_VC                  VC_FROM_TS         
121226145503+0530     2012-12-26-14.55.03.000000  2012/12/26 14:55:03
********  End of data  ********

Upvotes: 0

Vinayak Pahalwan
Vinayak Pahalwan

Reputation: 3005

In DB2 9.7, you can also use the TO_DATE function:

date(to_date(column_with_date,'DD-MM-YYYY HH:MI:SS'))

Also, you can use the TRANSLATE function

select 
   date(translate('DD/MM/YYYY',column-with-the-date,'xyz...'))
from
   table

Upvotes: 1

Related Questions