1

I have a column in file Delivery_date - '02/09/2020 15:30:00 PM' in string format . It can AM OR PM both. I need to convert it on TIMESTAMP format of oracle in informatica power center.

I have tried like below- TO_DATE(Delivery_date,'MM-DD-YYYY HH24:MI:SS')

But its not working for AM an PM. Kindly suggest

MT0
  • 143,790
  • 11
  • 59
  • 117
Yash
  • 39
  • 1
  • 7
  • Why do you want it in `MM-DD-YYYY HH24:MI:SS` and not `YYYY-MM-DD HH24:MI:SS` . doing `SELECT TO_TIMESTAMP(sysdate) FROM dual;` returns `YYYY-MM-DD HH24:MI:SS` format – Omari Victor Omosa Feb 20 '20 at 13:26
  • But it will work either way. as long as you have specified it is date – Omari Victor Omosa Feb 20 '20 at 13:55
  • @OmariVictorOmosa Your users can set whatever format they want in their own session (i.e. `ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-RR-MM AM SS:HH:MI'`) so you should never rely on a default format and should always supply a format model in the second argument to `TO_TIMESTAMP` and `TO_DATE`. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=b324daf6df796be2d4d7b8e9911f4dff): in the first example, the time is wrong and in the second example both century and time are wrong and third example doesn't even run (all without ever changing the SQL statement). – MT0 Feb 20 '20 at 14:10
  • Got you @MT0 . i have tested it works. – Omari Victor Omosa Feb 20 '20 at 14:12
  • @OmariVictorOmosa My point is not that it doesn't work for you on your computer with your database settings; its that it might not work for all users in the same way (see [default date format](https://stackoverflow.com/questions/50163432/oracles-default-date-format/50164234#50164234)) so if your query is run by someone in a different territory then the same SQL statement will give different results (or break) if you assume a default format model that is not guaranteed to be consistent between user sessions. If you want the query to work equally for everyone then pass an explicit format model. – MT0 Feb 20 '20 at 14:17

3 Answers3

0

If you want a TIMESTAMP data type then use TO_TIMESTAMP. Oracle does not allow both the HH24 and AM (or PM) format models as it will be ambiguous if the 24-hour clock and meridian indicator do not match; you will have to strip one of them out and since the AM/PM indicator is at the end of the string then that is easiest to remove using SUBSTR:

TO_TIMESTAMP( SUBSTR( delivery_date, 1, 19 ), 'MM/DD/YYYY HH24:MI:SS' )

db<>fiddle

(and if you just want a DATE data type, which in Oracle also has a time component, then use TO_DATE instead of TO_TIMESTAMP)

MT0
  • 143,790
  • 11
  • 59
  • 117
0

You can remove the AM or PM using reg_replace in your mapping transformation then add TO_DATE if the column is in date/time format 

REG_REPLACE('02/09/2020 15:30:00 PM', '( AM| PM)', '')

Since data is coming in as string and going out as time, your output epression i believe will be in date/time

Then you can add to_date to work.

TO_DATE(REG_REPLACE('02/09/2020 15:30:00 PM', '( AM| PM)', ''), 'MM-DD-YYYY HH24:MI:SS')

enter image description here

And if you want to force to the format --another way

TO_DATE(TO_CHAR(TO_DATE(REG_REPLACE('02/09/2020 15:30:00 PM', '( AM| PM)', '')), 'MM-DD-YYYY HH24:MI:SS'))

 #EXPLANATION
 /*REMOVE THE AM/PM: REG_REPLACE('02/09/2020 15:30:00 PM', '( AM| PM)', '')*/
 /*CONVERT IT TO DATE: TO_DATE(REG_REPLACE('02/09/2020 15:30:00 PM', '( AM| PM)', ''))*/
 /*CHANGE THE FORMAT: TO_CHAR(TO_DATE(REG_REPLACE('02/09/2020 15:30:00 PM', '( AM| PM)', '')), 'MM-DD-YYYY HH24:MI:SS')*/
 /*THEN RETURN TO DATE: TO_CHAR(TO_DATE(REG_REPLACE('02/09/2020 15:30:00 PM', '( AM| PM)', '')), 'MM-DD-YYYY HH24:MI:SS')*/
Omari Victor Omosa
  • 2,814
  • 2
  • 24
  • 46
0

Have you tried simply covering the full format?:

TO_DATE( Delivery_date, 'MM/DD/YYYY HH12:MI:SS AM' )

This should work just fine. For more format related questions please refere the documentation.

Maciejg
  • 3,088
  • 1
  • 17
  • 30