Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
You would think that storing dates in your database will be a fairly straightforward routine operation. On the surface, to you and I it is. But behind-the-scenes Access does a lot of work to make sure that your date-specific records are kept in order. Understanding how Access stores, retrieves, and formats date and time-based data can really help down the road. Access uses a serial number system to keep track of dates and times. Let's open up the Chapter 5 custom group and we'll take a look at our Dates table. Here we have a table listing of dates. If I run a query called DateSerial number, I have those dates listed down and I have a function that converts the date into the serial number representation.
You'll see that dates are represented by a whole number and they decrease as you go back in time, until you get to December 30th, 1899, which is represented by the serial number 0. Anything before that date gets negative; anything after that date is positive. Now the same thing applies to times. Let's run another query, this TimeSerial number query. You'll see that times are represented by fractions of a day and that makes sense. Starting at 12 midnight, at 0.0, at 12 noon which is halfway through the day is 0.5, three-quarters of the way through the day at 6 p.m. is 0.75, and if we get to 11:59 and 59 seconds, that's represented by the serial number 0.9999.
Now in these fractions we also have 0 and we already saw that 'd0 means that this time is applying to December 30th, 1899. Now you probably never try and extract a date from a single time field but if we look at our dates again we'll realize that the whole number is actually saying 0.0. So in Access' world, everything that happens on a specific date happens at 12 midnight. Let's see how these serial numbers affect working with comparison operators and dates and time data. When you have a field that stores date-only data, the relationship to each other is pretty straightforward.
May 11th is less than May 12th, May 12th is equal to May 12th and May 13th is obviously greater than May 12th. But if you include dates and times in the same records, for instance I have May 11th at 11:59:59, things get a little bit weird. The relationship for May 12th here is obviously less than May 12th. 1 second later at 12 midnight, Access says this is equal to May 12th and 1 second after midnight on May 12, Access thinks this is after May 12th or greater than.
So when using comparison operators with dates and times, it makes a difference if your date and time fields are together or discrete. Understanding the serial nature of how Access stores dates can clear up some evaluations down the road. It's always a good idea to understand what's going on behind-the-scenes in order to make better sense of how we can work with that on the front end.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98185 Viewers
80 Video lessons · 141372 Viewers
59 Video lessons · 59714 Viewers
52 Video lessons · 72938 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.