All Forums General
sarancts 1 post Joined 02/13
07 Feb 2013
Format yyyy.mm.dd hh:mm:ss to ddmmmyyyy:hh:mm:ss

Hi all,

I have a column in staging table with the format of yyyy.mm.dd hh:mm:ss. This needs to be converted to the format of ddmmmyyy:hh:mm:ss.

 

Consider the following example for the conversion.
2012.05.24 01:24:35 --->24may2012:01:24:35.

I keep getting Invalid timestamp error.

Any comments will help!!

Thanks guys.

saran
KS42982 137 posts Joined 12/12
07 Feb 2013

Not sure if you really want, ':' between DATE and TIME, but if you do not then you can try below -
SELECT CAST(UR_TIMESTAMP_FIELD AS TIMESTAMP FORMAT 'ddmmmyyyyhhmiss')
(Instead of hhmmss - you should try hhmiss)
If you really want ':' between your DATE and TIME, then you have to do CAST it as CHAR or VARCHAR and then SUBSTR DATE and TIME and concatenate (||) them by putting ':' in between.
 
 

dnoeth 4628 posts Joined 11/04
07 Feb 2013

What is the datatyoe of your input and what should be the resulting datatype?
Char -> timestamp?
Timestamp -> char?
Timestamp -> timestamp?
Dieter

Dieter

You must sign in to leave a comment.