Storing Any Date

[fauxanadu]

Is it possible to store dates before 01/01/0100 A.D. (such as for as database storing world events would require) using MS Access?

Verbose Explination

I need to be able to store dates before 100CE in a date field. This is for a world history database that I am building. I am currently storing dates in a database as unformatted text (14 characters: mm/dd/yyyy/[BCE|CE/]. This requires using alot of extra VBA code to validate the dates to ensure they are indeed valid dates and in a valid format. Using a date field, I figure that I can store dates from 01/01/9999 BCE to 12/31/0100 BCE and from 01/01/0100 CE to 12/31/9999 CE by storing a date in one field and BCE or CE in another field. However, these leaves a gap from 01/01/0099 BCE to 12/31/0099 CE that cannot be accounted for (a 197 year, 363 day gap by my calculations). Using this method would make it much easier to sort dates and also find the amount of time between two dates. Is this possible or is the method I am using the best way for this (I am currently using split on the date using the / as the character to explode the value)?

[nico5038]

Access will store dates in a numeric field being a relative daynumber.
This daynumber starts at 30-december-1899.
To get dates before the 30th December 1899 the relative daynumber will be made negative. This way the datediff will give correct results and I guess also for BC dates.

Nic;o)

[fauxanadu]

So is that a no?

[NeoPa]

I think it is within a Date/Time field I'm afraid.
It is possible, of course, to store dates as text instead. You would lose the ability to process or calculate on them as dates but you could handle most things I would have thought.
For date sorting in text you'd need the dates to be stored highest element first downwards. IE Year; Month; Day. Each element would have to be formatted to fit in a fixed length portion of the string (Years 5 chars to handle '-' - AD & BC would sort the wrong way unfortunately).
Alternatively, do something with a Long Integer, but I think that dates (as opposed to simple years) would require too many digits even for a Long.

[fauxanadu]

Thank you for the reply.

I think I am going to use an integer and a long to store the date. (48 bits/date = 6 bytes/date, less than the date field).

The integer will store the year, being from -32,768 to 32,767 with negative numbers being BCE and positive numbers being CE.

The long will store the day of the year and the time of day.

x = val(right(left(str(lngDay),6),3))
 

will be used to get the day of the year (0-999, but only 0-365 will be used) and

y = val(right(str(lngDay),6))
 

will be used to store the time of day. Any given time can be encoded as a series of number representing the decimal part of the day. For instance, there are 1440 minutes in the day. If it is 2:41 PM (14:31), then (14*60) + 31 minutes have passed = 871 minutes. Therefore I could use

y = val(right(left(str(871/1440),8),6))
 

to get the numeric value for the portion of the day, which in this case would be 604861.

Then I could just use the equation:

lngDayOfYear = (intDay * 1000000) + val(right(left(str(intHour*60 + intMinutes/1440),8),6))

to store the day and time and

intDay = val(right(left(str(lngDay),6),3))
intTime = round(val(right(str(lngDay),6))*1440)

to retrieve it. This would give me the minutes which could easily be turned into hours and minutes using modulo and division operators.

As an example, today and right now (March 31, 2007, 1:05 PM) could be stored as

intYear = 2,007
lngDay = 90,545,139

[NeoPa]

There's no reason you couldn't get that to work, but as most usages involve converting to string anyway, why complicate matters?
It wouldn't make much difference mind you so not a problem really.

[fauxanadu]

The reason I use numbers instead of strings it because they take up less space in the database. A date stored in a string that was 2 characters for the day, 2 for the month, 5 for the year, and 4 for the time would take up 13 bytes per date plus the space to store a string (4 bytes if I remember correctly, meaning 17 bytes total per). Storing it as a number would decrease the size to 6 bytes per date (2 bytes for the year, 4 for the day and time). The math is relatively easy to get the date to store it and retrieve it. The space is more vital. While the difference at this scale doesn't amount to much (11 bytes), when you consider a database with, say, around 100,000 records (which is what it will start at), that amounts to 11 bytes * 100,000 records = 1,100,000 bytes difference (1074.2 KB or 1.049 MB). The size difference is important for this application because it could easily span up to 10,000,000 records, equating to ~100MB difference.

(To be exact, using numerals =
6.00 B / Record * 10,000,000.00 Records = 60,000,000.00 B = 58,593.75KB = 57.22 MB
17.00 B / Record * 10,000,000.00 Records = 170,000,000.00 B = 166,015.63 KB = 162.12MB
162.12MB - 57.22MB = 104.9MB = 107,417.60 KB = 109,995,622.40 B

I appreciate the input but for this application I can't afford to use strings ^.^ Thank you for answering my question, however.

[NeoPa]

Very good reason :D
Bear in mind that these will be stored in fields within records which may be constrained to Word &/or Double-Word boundaries so the saving may not be precisely as calculated. Nevertheless, there will surely be considerable savings over so many records. Good choice.