Oracle to_date with pm / am

Question

I need to convert a string to Date in oracle.

The format of the string is as follows:

'08/11/1999 05:45:00 pm'

But the last position may change for m. I tried to do something like:

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

But give me error ORA-01855: AM / AM or PM / PM is required ... any idea?

+2

Abel juan rodríguez Dec 03 '14 at 10:56

source share

2 answers

to convert time to am and pm just specify am as shown below

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

+1

Exhausted Dec 03 '14 at 11:08

source share

Tony Andrews · Accepted Answer · 2014-12-03T11:19:01+0000

Try the following:

 to_date ( '08/11/1999 05:45:00 pm' , 'dd/mm/yyyy hh:mi:ss am' , 'nls_date_language=american' )

It seems that "and" instead of "am" and "pm" requires nls_date_language be set to "american".