Date Stuff–Functions

This post is the first in a 3-part series on Date functions in Access.

1. Date Stuff – Functions (this post)
2. Date Stuff – Using the functions
3. Date Stuff – Useful Examples

To see working examples, please see the following sample: DateStuff.mdb on my website: www.RogersAccessLibrary.com.

Date Functions

Microsoft Access has a variety of date functions to help you manipulate date values. Because there are so many functions, there are often multiple ways to do the same thing.  For instance, if I wanted to find the first day of this month, I could do:

DateAdd("d",1-DatePart("d",Date()),Date())
or
CDate(CStr(DatePart("m",Date())) & "/1")

And there are probably other ways as well. The purpose of this post is to explain how dates work and a few of the functions used to manipulate them.

What Are Dates?

First of all, in Access a date field stores both the date and time values as a double-precision number, that is, a whole number and decimal.  For instance, right this second, the date/time is 1/5/2012 6:53:04 AM. This value is actually stored as 40913.28686. 

The whole number (40913) represents the date (1/5/2012).  This is the number of days since 1/1/1900.  The decimal (0.28686) is the fractional part of the day and represents the time (6:53:04 AM).

The implications of this is that if I want to add a day to a date value, I can simply add 1.  If I want to subtract 12 hours from a time, I can simply subtract 0.5.  This is all well and good if I am dealing only with days.  But if we want to add a month to, or find someone's age in years or months, or add add 10 work days to a date, it becomes much more complicated.

This is where the built in date functions come in handy.  Used in combination, I can manipulate dates in terms of days, work days, weeks, months, and years; and times in hours, minutes, and seconds.

Date Functions

The some of the more useful date functions are:

1. Date() / Now()
2. DatePart()
3. DateSerial()
4. DateAdd()
5. DateDiff()
6. CDate()

Date() / Now()

The Date() function returns the current system date, that is, just the whole number part of the datetime (40913.0).  The Now() function returns the current date AND time, that is, both the whole number and decimal parts (40913.28686).

This is very useful (as we saw above) in determining dates relative to today's date.  I'll be using Date() in the examples below, but any date could be substituted.

DatePart()

The DatePart function allows you to determine the value of a given date in terms of specific intervals.  In other words, you can determine the year, month, day, week, quarter, etc, of a given date.  The syntax looks like this:

DatePart(interval, date [, firstdayofweek] [, firstweekofyear] )

Intervals:

yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

The following are some examples intervals returned using DatePart:

DatePart("yyyy", Date()) returns 2012.
DatePart("m", Date()) returns 1.
DatePart("d", Date()) returns 5.
DatePart("h", Now()) returns 6.

As you can see, DatePart can also be used to return times, but you need to use the Now() function because the Date() function doesn't return a time.

For more information, see the MS Knowledgebase article DatePart Function.

By the way, instead of the DatePart function, you can also use the Year(), Month(), Day() functions.  They work the same as the DatePart for each of their respective intervals.  I prefer to use DatePart because I can use it for everything.

DateSerial()

DateSerial(year, month, day)

The DateSerial function allows you to build a date value by giving values for the year, month, and day.  For instance, to build today's date, I could do this:

DateSerial(2012, 1, 5)

Of course, it's not all that useful when using literal values, but I can also use variable, which makes it extremely useful.  For instance, I could use it to create a date one year from today's date:

DateSerial(DatePart("yyyy", Date()), DatePart("m", Date()), DatePart("d", Date()))

So I can use the other date functions to build the separate intervals and then use the DateSerial to put them all together into a date value.

For more information, see the MS Knowledgebase article DateSerial Function.

DateAdd()

DateAdd(interval, number, date)

The DateAdd function allows you to add an interval to a given date.  The intervals are the same as those for the DatePart function, ie. year, month, day, week, etc. This is extremely useful because leap years and the different number of days in a month makes it difficult to accurately add some intervals by adding days.  The DateAdd function will accurately add intervals without any additional work on your part.

For instance, above, I built date one year from today with DateSerial.  However, I could do it much easier by using the DateAdd function:

DateAdd("yyyy", 1, Date())

A date six months is as easy as:

DateAdd("m", 6, Date())

For more information, see the MS Knowledgebase article DateAdd Function.

DateDiff()

DateDiff(interval, date1, date2 [, firstdayofweek] [, firstweekofyear] )

The DateDiff function allows you to find the interval between two dates.  The interval can be any of the standard intervals as seen in the DatePart function above, ie. year, month, day, week, etc.  This is the counterpart to the DateAdd function.

The DateDiff is really useful for determining age.  For instance, to find someone's age in years, I can to this (assumes DOB is a field holding the date of birth):

DateDiff("yyyy",[DOB],Now())

But I could just as easily find the age in months:

DateDiff("m",[DOB],Now())

For more information, see the MS Knowledgebase article DateDiff() function.

CDate()

The CDate() function is not a date function so much as a data conversion function.  You can use the CDate function to convert a string value into a Date.  The string has to be a recognizable date:

"January 2, 2012"

"12/1/2012"

You have to be a little careful if you don't use the US Standard date format of Month Day Year.  CDate will correctly interpret both "1 December, 2011" and December 1, 2011" as 12/1/2011.  But "12/1/2011" will evaluate to December 12 even if you meant it to be January 12.

While CDate is not a date function per se, it is sometimes useful for calculating certain date values.

For more information, see the MS Knowledgebase article: Conversion Functions.

Next time, I'll discuss various ways to use the Date Functions in Date Stuff – Using the functions.