Access: Dates and TimesBy
Time can be a moment or a millennium. It can be measured in small units like seconds or large units like years. Calendars break time up into periods like years, months, and days. And clocks use even smaller increments, like hours and seconds, to measure time. With all these different periods of time, how does Access store date and time values?
The answer is a “serial date.” A serial date represents a calendar date as the number of days that has passed since a predetermined fixed date. For Access, the fixed date is December 30, 1899. This makes day 1 = December 31, 1899. You might think that choosing January 1, 1900, would have made better sense, but the reasoning behind this choice has to do with Excel.
Excel stores date values in a similar fashion, but day 1 in Excel is January 1, 1900, which is a day later than in Access. So why the difference? When creating Excel, Microsoft chose to propagate a bug from Lotus 1-2-3 where February 29, 1900, was counted as a day; but 1900 wasn’t a leap year. By having the fixed dates for Excel and Access off by a day, the serial dates of the programs end up aligning on March 1, 1900, and continue to do so for every date after.
Why use a serial date? It’s currently the year 2018. On the next New Year‘s Day, the year will be incremented by one to 2019. There are 12 months in each year, which are represented by the numbers 1 to 12 or by the specific name of the month. Each month contains a varying number of days from 28 to 31. Of course, you already know all this. But how quickly can you determine how many days there are between May 25, 2018, and February 3, 2017? It would probably take much longer than it does to find the difference between 43,245 and 42,769. That’s much simpler. You can see how it might be easier to store values of time using a simple number for each day rather than referring to dates using those other time periods.
When you choose the date/time data type, Access stores a double-precision number that ranges from 657,434 for January 1, 100, to 2,958,465.99998843 for 11:59:59 p.m. on December 31, 9999.
The whole part of the number signifies the date. The fractional part of the number, if there is one, translates to the time. At 6 a.m., a quarter of a day has gone by, so the fraction is 0.25. At noon, half the day is gone, which is 0.5.
Figure 1 shows a query in Datasheet view. The first column, Num, contains a number from 1 to 10. This is chosen from the Numberz table. The next six columns are calculated. The date columns, Date0, Date1, and Date2, are dates in June 2018 and are formatted using yyyy-mm-dd”, “dddd. The serial date columns—Serial0, Serial1, and Serial2—correspond to Date0, Date1, and Date2, respectively, and have the exact same data as the date columns. What makes them look different is their Format property, which is #,##0.
Date0 is calculated using DateSerial(2018,6,[Num]). The DateSerial function lets you create a date from numbers. It has parameters to specify 2 to 4 digits for the year, 1 or 2 digits for the month, and 1 or 2 digits for the day. Date1 adds 10 to Date0. Date2 adds 10 to Date1. See Table 1 for the calculations and formats for the six date fields.
ENTERING DATE AND TIME
To enter both a date and time, enter the date, then add one or more spaces, and then enter the time. That’s all. Access will do its best to interpret what’s entered, and its rules are very flexible! All of these different formats will be recognized as the date for the last day of 2018:
* December 31, 2018
* 31 Dec
* 31 dec. 18
* 31 Dec
* decemb 31
If a year isn’t specified, the current year is assumed. If only two digits of a year are entered, Access will append 20 to the beginning of numbers less than 30 or add 19 to the front of numbers that are 30 or greater. This cutoff year of 30 is controlled by the advanced Windows Region settings for Date.
For the month, it’s okay to use a number or spell the name, which can be abbreviated to as few as three characters. Uppercase or lowercase doesn’t matter. For numbers, you can delimit with slashes (/) or dashes (-).
If Access is expecting a date, entering 12/32/18 will trigger an error since there aren’t 32 days in December. And at least three letters of a month name must be specified, so De 31, 2018 is also invalid. Monday, December 31, 2018 won’t be understood as a date because it contains extra information—the name of the day shouldn’t be included. Sometimes a typo might result in a valid, but unexpected, date such as 31 dec. 118.
Here are some examples of valid time entries: 6a, 8.30, 4.30p, 7:15, 2:00 PM, 1:15:30 am, and 16:0.
A shortcut to enter the current date and time is to press Ctrl+; for today’s date, add a space, then press Ctrl+Shift+: for the current time. These shortcut keys work in Excel, too.
Download this month’s database: SF_1806_DateTime.
Ctrl+; and Ctrl+Shift+: are shortcut keys to enter the current date and time, respectively.