Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle date format

Re: Oracle date format

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Thu, 4 Nov 2004 16:57:44 -0500
Message-ID: <>

"Bricklen" <bricklen_at_zyahoo.zcomz> wrote in message news:r1xid.76560$df2.69486_at_edtnps89...
| Bill wrote:
| > To make a long story short. I am using a 3rd party tool (web based) to
| > insert data into Oracle. We have no access to the source code of the
| > party tool. Everything works fine until we encountered the need to
insert a
| > datetime value. If just date, we have no problem, but when the time is
| > needed, then we have this problem.
| >
| > The 3rd party tool works with all databases, which we have
| > using JDBC. For all datatypes, except datetime, we have no problem.
| > only place we are encountering problem is with Oracle, we were able to
| > insert datetime into SQLServer okay. For datetime, I believe we need to
| > the data into the vendor's own format. (I maybe wrong.)
| >
| > Bill
| And you can't change your Oracle database(s) NLS_DATE_FORMAT settings?
| Is this one of those "one size fits all" generic apps that is the bane
| of every dba on earth? --> eg. it doesn't use bind variables, is coded
| to the lowest common denominator, and topples when put under a
| relatively light load?
| If not, then how is the app coded to know when it is
| inserting/connecting to Oracle? If that much is _not_ independent of
| db's, then why can't you get the vendor to change the code? I would
| think that it's the vendor's duty to provide you with code that works etc.
| Barring that, you could also use triggers to set your NLS settings on
| connect, or maybe an "instead of" trigger w/ view etc.

Looks like you've got a bug to report to the vendor, unless they don't officially support oracle.

Basically, then Bricklen's got you going down the right road. But could you give some idea on what the column datatype is? The format that you found ('DD-MON-RR HH.MI.SSXFF AM') is documented in the SQL Manual... it includes TIMESTAMP elements. Is your application working with fractional seconds in TIMESTAMP columns or whole seconds in DATE columns?

Is it possible/practical to train users to use a specific date/time format, even though it appears that the application does not check date formats? What formats does the application allow?

Read up on Oracle datatypes, NLS Parameters, and the format masks -- and meanwhile file a bug or a support call with your vendor. Also, look in the V$NLS_PARAMTERS view (which is accessible to all users).

++ mcs Received on Thu Nov 04 2004 - 15:57:44 CST

Original text of this message