wood burning stoves
The moose likes Oracle/OAS and the fly likes Oracle date storage? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Oracle date storage?" Watch "Oracle date storage?" New topic

Oracle date storage?

mallikarjun dontamsetti
Ranch Hand

Joined: Mar 18, 2011
Posts: 246

I am a java developer i am wonder how date is stored in back end oracle. I just stored two dates like this,

In DOB column it is storing like "1-1-88" in both columns. But when getting date field it is getting the correct data. How it is happening, Is the any buffer to store the era or something..
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

DATE in Oracle is stored in some internal ("binary") form. When handled by external systems, it is converted to/from the internal representation from/to the representation of the external system.
mallikarjun dontamsetti wrote:In DOB column it is storing like "1-1-88" in both columns.

I assume that you're using something like SQL*Plus or SQL Developer to display these values. In these cases, the date is converted to text for display using representation ("format") defined by your environment. I guess that your environment defines date format as something like DD-MM-YY (that is, two digit years only), which means that 1-1-1988 and 1-1-1788 get converted to the same text, though they are different in the database's native format.

If you're using SQL*Plus, you can execute the alter session set NLS_DATE_FORMAT='DD-MM-YYYY'; command; that will cause the date values to be printed with four digit year and you'll be able to distinguish the two dates apart even in the textual representation.

Meanwhile, there is a subtle bug in your code:
These commands cause the value for column dob to be implicitly converted using the current value of the NLS_DATE_FORMAT setting, which may differ on different computers or be influenced by other circumstances. If these commands were actually issued from Java, you should use bind variables instead; this avoids the database conversion from text do date altogether. If this is a part of some SQL script, you should use to_date function to explicitly define the date format to be used:
In production code, this is a must; I'd suggest using this even for just testing/experimental code. You'll eliminate possible errors from the beginning and learn a useful habit of specifying the conversion format everywhere.
I agree. Here's the link: http://aspose.com/file-tools
subject: Oracle date storage?
It's not a secret anymore!