Re: Oracle DATE datatype storage.

In article <1995Jul25.132639_at_ebi.ac.uk>, ditommaso_at_ebi.ac.uk (MATTEO DITOMMASO) writes:
|> In article <3v0gi8$cv1_at_helios.vanderbilt.edu>, Randy Fought <FOUGHTRL_at_CTRVAX.VANDERBILT.EDU> writes:
|> > 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
|>

This will actually take up more space. An Oracle NUMBER is a variable length type, up to 22 bytes in length. Precision and scale don't have any effect on the resultant length. I would recommend using DATE, since then you have the advantage of the predefined date functions, and live with the extra 3 bytes per row (which really isn't that much compared to other objects in the database). Received on Fri Jul 28 1995 - 00:00:00 CEST