Oracle to_date with p.m./a.m

39.3k Views Asked by At

I need to convert a string into a Date in oracle.

The format of the string is like this:

'08/11/1999 05:45:00 p.m.'

But the last position can change p.m or a.m. I tried to do some like:

to_date('08/11/1999 05:45:00 p.m.', 'dd/mm/yyyy hh:mi:ss a.m./p.m.')

to_date('08/11/1999 05:45:00 p.m.', 'dd/mm/yyyy hh:mi:ss am/pm')

But return me an error ORA-01855 : AM/A.M. or PM/P.M. required... any idea ?

3

There are 3 best solutions below

0
On BEST ANSWER

Try this:

to_date
  ( '08/11/1999 05:45:00 p.m.'
  , 'dd/mm/yyyy hh:mi:ss a.m.'
  , 'nls_date_language=american'
  )

It seems that "a.m." and "p.m." rather than "am" and "pm" require nls_date_language to be set to "american".

1
On

to convert time for am and pm, just give a.m. like below

to_date(UPPER('08/11/1999 05:45:00 p.m.'),'dd/mm/yyyy hh:mi:ss a.m.')

hope this might help. please refer https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm

0
On

I tried this case. Here is the right answer: TO_DATE('3/9/2022 9:35:00 PM', 'MM/DD/YYYY hh:mi:ss am', 'nls_date_language=american')