Search code examples
javaoracle-databasedatetimestampnls

Oracle Date Literal [DD-MON-RR HH.MI.SSXFF AM]

I'm trying to use the timestamp format [DD-MON-RR HH.MI.SSXFF AM] to insert my date.

I can't modify the database settings in anyway possible, and I have to insert the date through a JAVA's string format (I can't modify the class that defined it either).

Having said that, I need to literally reconstruct the format string-by-string without tempering the other class/db.

The nls settings for date is DD-MON-RR. 12-JUN-2012 and 12/JUN/2012 worked perfectly fine.

But I find it difficult to recreate the timestamp part of the date.

Listed below is a few format I've tried.

'12-JAN-12' < success
'12/JAN/2012' < success
'12/JAN/2012 10.30.25.000 AM < failed
'12/JAN/2012 10:30:25.000 AM < failed

Did messed up the : or .? Or was the zero(s) aren't enough for miliseconds? Been in this trouble for hours now.

Thanks in advance.

EDIT

After a few reasoning sessions, the seniors gave their permission to alter the model class. Everything's good now. Thanks for the help and suggestions.

Solution

  • Since you must send a string to the DB, you must rely on the implicit conversion of the oracle DB to the DATE type.
    Since you can't change the DB settings, the only thing I can suggest is changing the session settings.
    So, if you can run commands against the DB, try: 

    Statement st = conn.createStatement();
st.execute("alter session set NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss'");

    (or some other format (it's not recomended to use mon in your format because it might involve NLS_TERRITORY too))