How to convert VARCHAR to TIMESTAMP in DB2

Question

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

Solution

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

Comments

[Lilantha Lakmal]

When I usaed first step like below DB2 Database Error: ERROR [22007] [IBM][DB2/HPUX-IA64] SQL0969N There is no message text corresponding to SQL error "-20448" in the message file on this workstation. The error was returned from module "SQLRI1DC" with original tokens "121226145503+0530 DD-MM-YYYY HH:MI:SS". SQLSTATE=22007

[Clockwork-Muse]

I'm not sure who to poke at here - the answer-er, for providing the 'wrong' date/time parsing string, or the questioner, for not recognizing the formatting differences and adjusting.

[CRPence]

@Lilantha Lakmal -20448 "An invalid datetime format was detected; that is, an invalid string representation or value was specified." As the followup comment suggests, the answerer did not provide the desired format-string, so adjustments are required; see my recent answer for the desired TO_DATE syntax, but then the additional requirement from the OP to get the VARCHAR result.

[CRPence]

@Vinayak Pahalwan The OP shows 'YYMMDD' as the date portion, and 24hr time implied per 14, thus 'HH24MISS' for the time portion. Presumably, as I can not test, a functional TRANSLATE scalar example [but coded with an assumed '20' as first two digits of YYYY] that can be inside the DATE() is?: translate( /* expression */ '20Yy/Mm/Dd Hh:Uu:Ss' , /* to-str-exp */ '121226145503+0530' , /* from-str-exp */ 'YyMmDdHhUuSs+zone' ) though unavailable on db2 for i