MS-Access / Getting Started

Date and Time Functions

A number of functions deal specifically with date and time. These are included in this section.


The Now function returns the current date and time:

MsgBox Now

This displays the short date and time formats from the Windows Control Panel.


Date returns the current date in short format as defined in the Windows Control Panel:

MsgBox Date


The Time function returns the current system time:

MsgBox Time

The preceding line is an example of setting the time to 11 minutes past one in the afternoon.


DateAdd allows the addition and subtraction of a specified time interval to a date. The syntax is as follows:

DateAdd (interval, number, date)

Interval is a string that expresses the interval of time you want to add.
The following table provides a list of interval types:

Time Period 	Interval
Year 		Yyyy
Quarter 	Q
Month 		M
Day of Year 	Y
Day 		D
Weekday 	W
Week 		Ww
Hour 		H
Minute 		N
Second 		S

Number is a numeric that determines the number of intervals you want to add. A negative value is allowed and will prompt subtraction from the date.

Date is the date being added to, or the name of a variant containing, the date. This example will add one month to January and return 1-Feb-03:

MsgBox DateAdd ("m",1,"1-Jan-03")

The following will add two weeks and return 15-Jan-03 (depending on your date format):

MsgBox DateAdd ("ww",2,"1-Jan-03")

The following will subtract two days from 1 January 2003 and return 30-Dec-02:

MsgBox DateAdd ("d", -2, "1-Jan-03")


The DateDiff function returns the number of time intervals between two specified dates:

DateDiff (interval, date1, date2)

Interval is a string expression based on the following table to show the type of interval. The date1 string indicates the start date, and date2 the end date.

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

The following is an example of DateDiff:

MsgBox DateDiff("m", "1-jan-03", "15-mar-03")

This will return the result 2 because there are two months between 1-jan-03 and 15-mar-03. Note that it truncates to the lower month. If date2 was 30-mar-03, it would still return 2. Only when date2 is 1-apr-03 will it return 3.


The DatePart function returns a specified part of a given date:

DatePart (interval, date)

Interval is the time period based on the following table, and date is the date you want to inspect.

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

The DatePart syntax is as follows:

MsgBox DatePart("q", "1-mar-03")

This will return the result 1 because 1-Mar-03 is in quarter 1.
The following will return the result 3 because March is the third month:

MsgBox DatePart("m", "1-mar-03")


DateSerial returns the date serial for a specific year, month, and day entered as integers. The date serial is the actual number representing that date:

DateSerial (year, month, day)

where year is a number between 100 and 9999 or a numeric expression; month is a number between 1 and 12 or a numeric expression; and day is a number between 1 and 31 or a numeric expression.

For example, the following will return the value 37686, which is the date 6-Mar-2003:

MsgBox CDbl(DateSerial(2003, 3, 6))

You need to use CDbl (convert to double) in this code, or the message box will display the date per the format in the Windows Control Panel rather than as an actual number.

[Previous] [Contents] [Next]