CODEWITHSUNDEEP
sqloracle-databasedatedate-formatinformatica
Vincent G
Vincent G

Reputation: 361

Can't find good Date Format : impossible parsing

I'm trying to convert the following String to a datetime format: 

Sat Jan 14 13:55:34 CET 2017

If we consider that my date is in the in_OutPut3 variable, I'm trying to use the following format pattern : 

TO_DATE(in_OutPut3,'DY MON DD HH:MI:SS Z YYYY')

I receive the following error :

The system failed to parse the date format : 'DY MON DD HH:MI:SS Z YYYY'.

Do you have any idea what is this date format ?

Upvotes: 0

Views: 130

Answers (3)

Samik
Samik

Reputation: 3455

You can just ignore the timezone while converting to Informatica Date type. Anyway, Informatica won't retain timezone information after converting to Data datatype. Put some symbols like underscore(_) to match 'CET'

TO_DATE(in_OutPut3, 'DY MON DD HH24:MI:SS ___ YYYY')

Also, looks like you have the hours in military format. In that case you have to use HH24.

Upvotes: 0

BriteSponge
BriteSponge

Reputation: 1054

You have a timezone embedded in your string so try using the TO_TIMESTAMP_TZ function. TO_DATE is not designed to work with timezones.

The following works on 11g;

select to_timestamp_tz('Sat Jan 14 13:55:34 CET 2017','DY MON DD HH24:MI:SS TZR YYYY') from dual

Upvotes: 1

marcothesane
marcothesane

Reputation: 6749

This one worked for me:

to_timestamp('Sat Jan 14 13:55:34 CET 2017','Dy Mon dd hh:mi:ss ZZ yyyy');

Marco

Upvotes: 0

Related Questions