Re: Oracle DATE datatype storage.
Date: 1995/07/26
Message-ID: <3v5iga$8qv_at_tuna.hooked.net>#1/1
>> Internally Oracle stores the DATE datatype in seven bytes.
>> One byte for each of the century, year, month, day, hour, minute
>> and second.
>>
>> We don't care about the hour, minute and second and we have
>> many dates to store. This seems quite wasteful to store this in
>> seven bytes when it could be done in four. Is there a way to internally
>> store the date in four bytes?
>>
>> Randy
>your best bet is to store it as a NUMBER(8) YYYYMMDD
>instead of using the ORACLE date format.
>
>M.diTommaso
>ditommaso_at_ebi.ac.uk
If you decide to use NUMBER(8) instead of the Oracle date format, be aware
of some possible nasty side effects:
- No automatic validation of valid dates (ex. July 32, 1995 would be
accepted unless you write triggers to check for it)
- No possibility to use Oracle date functions such as adding and
subtracting days unless once again you programmatically convert every
occurence to a date, do your calculation, and convert it back to a number
- No access to the date format masks if your application must run in
different countries with different ways of displaying the date. In
particular, the NLS init.ora parameters help a great deal in this to set a
default date format!
We have run into this very issue as well and decided to use the DATE type. We decided it wasn't worth the space savings. Just some thoughts to consider!
Walter Schenk
SoluTech Consulting Services Inc
Received on Wed Jul 26 1995 - 00:00:00 CEST