Oracle date storage?

[mallikarjun dontamsetti]

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

     Insert into XYZ (name,dob,..) values('abc','1-1-1988',...)
      Insert into XYZ (name,dob,..) values('eee','1-1-1788',...)
     

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 Vashko]

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:
Insert into XYZ (name,dob,..) values('abc','1-1-1988',...) Insert into XYZ (name,dob,..) values('eee','1-1-1788',...) 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:
Insert into XYZ (name,dob,..) values('abc',to_date('01-01-1988', 'DD-MM-YYYY'),...); Insert into XYZ (name,dob,..) values('eee',to_date('01-01-1788', 'DD-MM-YYYY'),...); 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.