Format yyyy.mm.dd hh:mm:ss to ddmmmyyyy:hh:mm:ss

General

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.

2 REPLIES
Enthusiast

Re: Format yyyy.mm.dd hh:mm:ss to ddmmmyyyy:hh:mm:ss

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.

 

 

Senior Apprentice

Re: Format yyyy.mm.dd hh:mm:ss to ddmmmyyyy:hh:mm:ss

What is the datatyoe of your input and what should be the resulting datatype?

Char -> timestamp?

Timestamp -> char?

Timestamp -> timestamp?

Dieter