Re: help !!! use number or date to store date ????

droth_at_dr.ultranet.com (David Roth) writes:
>sptong_at_singnet.com.sg (Tong Siew Pui) wrote:
>>If I want to store the month and year in a table, should I use
>> year number(4)
>> month number(2)
>>or
>>should I use date to store the value but default the day to 1st of the
>>month ???
 

>Oracle stores dates as a number.

This is not the case. Dates are stored in a "BCD-ish" format of seven bytes length (8 bytes internally). The smallest unit is the second. (You can time events in Oracle7.x to the hundredth of a second using the v$timer system "table", but the DATE datatype has a granularity of the second.)

The internal DATE storage format is documented in the OCI and precompiler manuals.

To the original poster: if you look up the storage formats for DATE, NUMBER, and VARNUM in either the OCI or a precompiler manual, you can compute how many bytes are required to hold the information. In your case, I'd guess that VARCHAR2 would be optimal. You would have to do some parsing and recontruction, however, if you need to go back to a character string that can be converted to Oracle DATE format. You could do this in PL/SQL.
>There are masks that allow you to convert a date to a character string
>in virtually any format.

True. Received on Mon Aug 21 1995 - 00:00:00 CEST