databasedev.co.uk - database solutions and downloads for microsoft access

Date Functions and Syntax Examples

Date Functions in Microsoft Access:

MS Access is a very versatile data handling package. It has numerous inbuilt function which enable formulating complex queries. The date functions of Microsoft Access include Date, DateAdd, DateDiff, and DatePart. They are very helpful in data handling.

The Date() Function

This function returns the current system date. It has no parameters and can be used in a form field or as a criteria in a query.

The DateAdd Function

This function can be used to add or subtract a specified interval to any date. It returns a Variant containing the resultant date. For instance, if you want to get the date 45 days from now, you can use the DateAdd function.This function will not return an invalid date.

DateAdd Syntax:

DateAdd(period, increment, date)

<period> This required parameter denotes the interval by which you want to increase the date. It is a string expression. Any period from seconds to years can be specified. This parameter can have the following variations:
yyyy Year
q Quarter
m Month
y Day of Year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
<increment> It is the number of periods you wish to add. A positive number gives you a date in the future and a negative is used for returning a past date.
<date> This is the date in question to which the <period> is to be applied.

DateAdd Function Examples

DateAdd("m", 1, "30-Jan-99") - It will return 28-Feb-99.

DateAdd("w",1,Date()) - This will return a date one week from the current date. Note that the same date can be retrieved by the following:-

DateAdd("d",7,Date())

Variables can also be used as parameters in this function. Newdate = DateAdd("d",[addperiod],[oldDate])

The DateDiff Function

This function returns the differential interval between two dates. You can choose the type of interval in which you want the difference.

DateDiff Syntax:

DateDiff(period, firstdate, seconddate[, firstdayofweek[, firstweekofyear]])

<period> This argument specifies the type of interval in which you require the date difference. This parameter can have the following variations:
yyyy Year
q Quarter
m Month
y Day of Year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
<firstdate> This is one of the two required dates.
<seconddate> This is the second of two required dates.
<firstdayofweek> This optional argument specifies the first day of the week. if none is given, Sunday is the default. It has the following settings:
vbUseSystem 0 Use the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday
<firstweekofyear> This optional argument specifies the first week of the year. If none is given, the week of January 1 is the default. It has the following settings:
vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 week in which January 1 occurs (default).
vbFirstFourDays 2 first week having at least four days in the new year.
vbFirstFullWeek 3 Start with first full week of the year.

If the <firstdate> is later than the <seconddate>, this function returns a negative value.

If <firstdate> or <seconddate> is enclosed in double quotation marks (" "), and the year is omitted, the current year is put in your code each time the <firstdate> or <seconddate> expression is evaluated. Thus, the code can be year independent and can be used in separate years.

DateDiff Function Examples

DateDiff("ww", "1-1", Now()) - This example returns the number of weeks elapsed since January 1. note that the year is not mentioned in the specified date.

(DateDiff("d", joindate, Date()))/365.25 - If joindate gives the date of joining, this function will return the number of years the employee has worked.

The DatePart Function

This function returns a specified portion of a given date.

DatePart Syntax:

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

<period> This argument specifies the type of interval in which you require the date difference. This parameter can have the following variations:
yyyy Year
q Quarter
m Month
y Day of Year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
<date> This is the required date.
<firstdayofweek> This optional argument specifies the first day of the week. if none is given, Sunday is the default. It has the following settings:
vbUseSystem 0 Use the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday
<firstweekofyear> This optional argument specifies the first week of the year. If none is given, the week of January 1 is the default. It has the following settings:
vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 week in which January 1 occurs (default).
vbFirstFourDays 2 first week having at least four days in the new year.
vbFirstFullWeek 3 Start with first full week of the year.

DatePart Function Examples

DatePart("q",[newdate]) - This will extract the quarter of the argument [newdate].

DatePart ('m', #11/12/2006#) would return 12

All of the date functions can be used independently and in conjunction to form powerful codes for handling data.