Access: DatePart Function

In Access, the DatePart function returns the specified part of a given date.

The syntax for the DatePart function is:

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

You can use the DatePart function to inspect a date and return a specific interval of time. For example, you can use DatePart to calculate the day of the week for an order’s ship date or the current hour.  

interval is the interval of time you use to return. The following is a list of valid  interval values.

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

date is the date value that you wish to evaluate.

firstdayofweek is optional argument that specifies the first day of the week. If not specified, Sunday is assumed. The following is a list of valid parameters for firstdayofweek.

Constant	Value	Description
vbUseSystem	0	Use the NLS API settings
vbSunday	1	Sunday (default used)
vbMonday	2	Monday
vbTuesday	3	Tuesday
vbWednesday	4	Wednesday
vbThursday	5	Thursday
vbFriday	6	Friday
vbSaturday	7	Saturday

firstweekofyear is optional argument that specifies the first week of the year.  If not specified, the first week is assumed to be the week in which Jan 1 occurs. The following is a list of valid parameters for firstweekofyear.

Constant	Value	Description
vbUseSystem	0	Use the NLS API settings
vbFirstJan1	1	Start with the first week in which January 1 occurs (default).
vbFirstFourDays	2	Start with the first week in the year that has at least 4 days
vbFirstFullWeek	3	Start  with the first full week of the year

Examples

DatePart ('m', #12/17/2003# )            returns 12 DatePart ('yyyy', #11/05/2001# )         returns 2001 DatePart ('d', #11/05/2005#)             returns 5

VBA Code

Dim MyNumber As Integer  MyNumber = DatePart('m',#10/06/1998#)

This example takes a date and, using the DatePart function, displays the month of the year in which it occurs. Now the MyNumber variable would contain the value of 10.

SQL query

You can also use the DatePart function in a query.

SELECT DatePart('d',#05/05/1985#, Now()) AS Expr1 FROM Orders