Navigation:  Articles > Jun-1998 >

Saving Time Using Date/Time Functions

Previous pageReturn to chapter overviewNext page

Angela J. Reeves Jones          
 
This might not be everything you want to know about Access's Date/Time functions, but it's all the cool stuff. Angela explains and compares the most useful of Access's 22 Date/Time functions, includes some cool coding tricks, and tells you which functions will give you the best performance.
 
I remember the moment when I was first confronted with the daunting list of Date/Time functions. My new boss had just hired me as a full-time Access developer, and I was working on one of my first queries in my new job. I had some experience with smaller databases, but nothing like the extensive application he'd assigned me to build for the training department of this multi-billion-dollar pharmaceutical company. There were rigid government regulations about how often the company's people had to be retrained and retested. To make sure I had the appropriate level of stress, I was reminded that managers' jobs are threatened if their department falls behind on training. My use of dates and times in this query wasn't just an interesting technical issue.
 
I was so hopeful as I clicked on the expression builder from my query criteria line -- I'd seen the "Date/Time" category listed in the builder, and I believed something there would help me. But when 22 functions popped up with indistinguishable names like CDate, Date, Date$, DateAdd, DateDiff, DatePart, DateSerial, and DateValue, I was totally overwhelmed. I struggled on, and everything worked, but it wasn't pleasant.
 
Thanks to that assignment (and others since), I've had to dig deeply into each of those functions, as well as Access 97's new CVDate. I've come to deeply appreciate the rich functionality these functions offer. This article won't cover all of them in depth, but I'll hit the ones I return to most often. The sample database (DateTime.mdb, available in the accompanying Download file) is for a company that does Everyday (ED) and Seasonal (SEAS) business. The SALES table includes monthly ED and SEAS totals by customer (the CUSTOMER and SALESMAN tables are more straightforward). This article will cover most of the queries, but feel free to browse the database to find others I haven't discussed here (see the sidebar, "The Rest of the Story"). I'm going to start off by describing the simplest functions and use those as a basis for working up to the more complicated functions.
 
The simpler functions
 
The easiest functions to explain are Date(), Now(), and Time(), because they simply return (in order) the system's current date (month-day-year), date plus time (month-day-year-hour-minute-second), and time (hour-minute-second). Date$() and Time$(), respectively, return the system's current date and time in string formats, not date formats.
 
The next simplest are what I'll refer to as the parsing functions: Month(), Day(), Year(), Hour(), Minute(), and Second(). They parse out one part of a date, returning the month, day, year, hour, minute, or second of the date/time you pass to them. For example, if the system date/time is May 15, 1998, at 3:02:57 p.m., then here are the results of a few functions:
 

Month(Date()) = 5

Day([ShipmentDate]) = 1 '

'Day = 9 because the ship date is the 9th of the month 

Year(Now()) = 1998

Hour(Time$())=15 

'Hour = 15 because it is 3 PM. 

'At 3 AM the Hour would be 3

 
 
CDate() and CVDate() both convert date expressions into Dates and are only somewhat complicated -- at least, if you understand how Access stores dates and times. Values of the Date data type are just real numbers, where the value to the left of the decimal represents a date and the value to the right of the decimal represents a time. Dates are calculated from December 30, 1899, and times are calculated from midnight.
 

199806_aj1
Some examples are in order here. If you're looking just at dates, the number 1 = December 31, 1899, and the number 2 = January 1, 1900. If you also consider times, the number 2.4 = 2:24:00 p.m. on January 1, 1900. The earlier example of 3:02:57 p.m. on May 15, 1998, converts to a Double date type as follows (use pound signs to let the function know that you're passing in a date string, as shown in Figure 1).
 

CDbl(#5/15/98 3:02:57 PM#) = 35930.6270486111

 
 
Likewise, CDate() converts that same number back to a recognizable date. Since dates are stored as numbers, the various parsing functions can parse the number as well as they can parse a date:
 

CDate(35930.6270486111) = 5/15/98 3:02:57 PM

Month(35930.6270486111) = 5 

Day(35930.6270486111) = 15 

Year(35930.6270486111) = 1998

 
 
The only difference between CDate() and CVDate() is that CDate() returns a Date data type value, and CVDate() returns a Variant data type value.
 
The other simple Date/Time functions are IsDate(), Timer(), and Weekday(). IsDate() returns True if you pass it a legitimate date or time value, and returns False otherwise. Timer() returns a number that represents how many seconds past midnight your system date is at that moment, so Timer() = 1 when it's midnight plus one second (0:00:01). Weekday() returns an integer that indicates on which day of the week a date falls, where Sunday = 1 and Saturday = 7.
 
The not-quite-as-simple functions
 
DateSerial() and TimeSerial() are very similar, as the syntax for them suggests:
 

DateSerial(year, month, day)

TimeSerial(hour, minute, second)

 
 
In fact, these are really simple functions with confusing names. Just pass them a year-month-day or an hour-minute-second, and you get back a complete date or time. At first glance, these functions seem almost moronic -- why would I need a function to give me the date if I know the year, month, and day? However, the beauty of DateSerial() and TimeSerial() is that the parameters can be calculated or passed in individually. For example, if you have a form where you want the default value of a control to be the first day of the month, no matter what month or year you're in, you could use:
 

 = DateSerial(Year(Date()), Month(Date()), 1)

 
 
The expression effectively passes in the first day of this month. I think this is a beautiful line of code. You don't have jump through the usual hoops of figuring out what day of the month it is and subtracting that many days from Date(). All you have to do is use DateSerial to build the date you want. Likewise, if you want sales figures for all of last year, you can set criteria as follows:
 

Between DateSerial(Year(Date())-1, 1, 1) AND 

   DateSerial(Year(Date())-1, 12,31) 

 
The TimeSerial() function can be used the same way.
 
There's a function for calculating the date -- DateAdd() -- and it has its uses. I'll discuss when to use DateAdd() and when to use the Serial functions later in the article. Making the right choice between these two functions will impact your performance.
 
DateValue() and TimeValue()
 
DateValue() and TimeValue() are also relatively simple functions with semi-ambiguous names. Both functions require valid date/time expressions as arguments and return the Variant date or time represented by that expression.
 
So what's the difference between DateValue() and CVDate(), since they both take in valid strings or numbers and return Variant dates? As far as I can tell, the only difference is performance. DateValue() works slightly faster than CVDate() when you pass a string or number expression. CVDate() works much faster when you pass in a date type expression. CDate and CVDate have similar runtimes. The question is, why would you use CVDate() or CDate() if your information is already formatted as Date type data? A larger question is, when should you convert your non-Date type data into Date type before using it?
 
The answer to these questions is interesting. First, if it's at all possible, store your data in Date type for faster calculations. But, if you need to attach to a text file with dates represented by strings (or something torturous like that), don't assume that you'll have to convert that data to Date type at runtime. It's very important to be extremely cautious with any of the conversion functions, since you sometimes might get unexpected results.
 
As a test, I changed a Date/Time field in the sample database to a text field. Since the field contained valid dates, the result of the conversion was a text field containing valid date strings. All of my queries that used the field continued to run accurately using this text field. However, the queries also ran at least 10 times slower. When I actually used the DateValue() or CDate() functions in my query to convert the data from text back to dates, the queries slowed to such a painful speed that I never got to the end of some of them.
 
DateDiff() and DateAdd()
 
The DateDiff() function does exactly what you might expect from its name -- it calculates the number of intervals between two dates or times. The syntax is:
 

DateDiff(interval, date1, date2[, firstdayofweek[, 

   firstweekofyear]]) 

 
The interval argument is the part that's hard to remember, but it's easy to locate in Help. The valid values for the interval parameter are shown in Table 1.
 
Table 1. Valid interval entries for DateDiff() and DateAdd().

Interval

Meaning

yyyy

Year

q

Quarter

m

Month

y

Day of year

d

Day

w

Weekday

ww

Week

h

Hour

n

Minute

s

Second

 
 
There are several things to remember with the DateDiff() function:

The value passed into the interval argument must be a string, so don't forget to put quotation marks around your interval choice.
If date2 is before date1, the function returns a negative number.
Answers are rounded to the nearest integer.
The intervals "day of year" (y) and "day" (d) can be used interchangeably.
You only need the firstdayofweek and firstweekofyear arguments if you're calculating weekdays "w" or weeks "ww".

 
The "w" and "ww" intervals are a little confusing -- they both return weeks, but "w" returns the number of physical weeks between the two dates. The "ww" interval returns the number of calendar weeks. The "ww" interval works by counting how many of the "first day of week" fall between the two dates, so it depends on which day you specify as the beginning of the week. See the Help if you're confused; I usually use "w", as I normally want to calculate the weeks by number of days, not by calendar weeks (see Figure 2).

199806_aj2 Figure 2
 
DateAdd() also does exactly what you'd expect it to do -- it adds (or subtracts) a specified amount of time to a date. The syntax is:
 

DateAdd(interval, number, date)

 
 
Again, interval is a string expression that specifies what unit of measure you want to add (it uses the same values as shown in Table 1). Number is how many of the interval you want to add or subtract (negative numbers take away time). Date is the starting date you want to manipulate.
 
The confusing thing is that we can also add or subtract years, months, days, hours, minutes, or seconds by manipulating DateSerial() or TimeSerial(). So when should you use DateAdd(), and when should you use one of the Serial functions?
 
The good news is that it's hard to go wrong with either, at least in terms of performance. I tested the following two criteria lines head to head with a huge test deck and an excellent stopwatch to figure out which one ran fastest:

Between DateAdd("yyyy",-1,[Enter Date]) And DateAdd("yyyy",-4, [Enter Date])
Between DateSerial(Year([Enter Date])-1, Month([Enter Date]),Day([Enter Date])) And DateSerial(Year([Enter Date])-4, Month([Enter Date]),Day([Enter Date])

 
It might seem counterintuitive, because the DateSerial statement is so much longer and involves so many more functions, but the two criteria lines are head to head for performance. You can run these two statements in the queries "Last4Years-DateAdd" and "Last4Years-DateSerial" in the sample database to see for yourself.
 
There are a couple of places where one function shines. For example, when you need to know year-to-date figures or cumulative numbers from the beginning of the quarter, you should use DateSerial() to trap for the beginning of the time period. It's much quicker than trying to calculate how many days to add or subtract.
 
The other case is when you need to add or subtract something other than year, month, or day -- such as week or quarter. DateSerial() can't help you there, so you'll need to turn to DateAdd() for those less common calculations.
 
DatePart()
 
DatePart() returns an integer containing the specified part of a given date. The syntax is:
 

DatePart(interval, date[,firstdayofweek[, 

   firstweekofyear]]) 

 
 
In this case, interval is almost the same as for DateDiff() and DateAdd(). Again, firstdayofweek and firstweekofyear are only used with "w" and "ww", and they work the same way they do in DateDiff(). Day of year "y" and day "d" aren't the same in DatePart() as they are in DateDiff() and DateAdd():

"y" returns the number of days since the beginning of the year.
"d" returns the number of days since the beginning of the month.

 
DatePart() can perform the same functions as Year(), Month(), Day(), Hour(), Minute(), and Second(). However, if you're looking to extract something else (quarter or week, for instance), you'll be forced to use DatePart(). So the question is, which is the better way to extract the year, month, day, hour, minute, or second: the dedicated function or DatePart?
 
Here, intuition would probably lead you to the right answer. I've found that it's slightly faster to use the specific Year(), Month(), Day(), Hour(), Minute(), and Second() functions. First of all, they're easier to remember (is the right interval value "yyy" or "yyyy"?) and therefore faster to program. More importantly, they seem to run roughly 10 percent faster. This is based on my highly unscientific method of manually counting seconds as queries run, but to try it yourself, run SalesmanYTD-DatePart and SalesmanYTD-Month in the sample database. For the most obvious results, use salesman 200 and Enter Date 12/98. A few seconds' gain is a few seconds' gain, after all, and especially important if you're embedding queries.
 
Out of time
 
As an overall rule, I've found that the less typing you have do to get the right answer (and the easier it is to read your functions), the faster you'll get the answers. In other words, choose the function that uses the fewest calculations and the fewest embedded functions, and you've probably chosen the right one. The key is to remain familiar with all your options, and when in doubt, run side-by-side examples of different options to see them in action. Have fun, and happy timing and dating!
 

 
Your download file is called DATETIME.ZIP   in the file SA1999-06down.zip

This is found in theon this page

 
 
Angela is a systems analyst/developer with Apex Consulting Group in Northbrook, IL. Her primary professional motivation is to save her clients time so they can have dinner with their families.
 
 
Sidebar: The Rest of the Story
 
For other examples of intricate Date/Time functions, look at the sample database DateTime.mdb. The report rptKeyAccounts shows a one-page overall history of every customer account that's ever spent more than $5,000 in a year (Key Accounts). Which accounts are Key Accounts is determined in the union query KeyAcctsList. The report itself is a complex series of IIF statements, but it gets the job done.
 
The report rptSalesmanProfile-Detail is of interest in the way it assigns a status to each customer. Also available are several queries whose names include RUN or YTD, both of which offer interesting uses of Date/Time functions. So if you have some extra browsing time, have fun digging in!