MS Access DatePart() Function

The DatePart() function in MS Access extracts a specific part (year, month, day, hour, minute, etc.) from a date value. It's a very useful function for working with dates and performing date-based calculations or analysis.

DatePart(): Definition and Usage

DatePart() lets you isolate individual components of a date or time value (year, month, day, hour, minute, second, week number, etc.). This is extremely helpful for filtering, grouping, or calculating based on specific parts of a date. The function returns an integer value representing the extracted part. You can optionally specify the first day of the week and how the first week of the year is defined, which affects calculations involving weeks.

Syntax

Syntax

DatePart(datepart, date, firstdayofweek, firstweekofyear)

Parameter Values

Parameter Description
datepart The part of the date to extract. Options:
  • yyyy: Year
  • q: Quarter
  • m: Month
  • y: Day of year
  • d: Day
  • w: Weekday
  • ww: Week
  • h: Hour
  • n: Minute
  • s: Second
This is required.
date The date value. This is required.
firstdayofweek (Optional) The first day of the week (0: system setting, 1: Sunday, 2: Monday, etc.). Defaults to 1 (Sunday).
firstweekofyear (Optional) How the first week of the year is defined (0: system setting, 1: first week containing Jan 1st, 2: first week with at least 4 days, 3: first full week). Defaults to 1 (first week containing Jan 1st).

Examples

Extracting the Year

This example extracts the year (2017) from the date September 5th, 2017.

Syntax

SELECT DatePart("yyyy", #09/05/2017#);
Output

2017

Extracting Other Date Parts

These examples show how to extract the month and weekday.

Syntax

SELECT DatePart("m", #09/05/2017#); --Month
SELECT DatePart("w", Date());      --Weekday (assuming Sunday=1)
Output

9
(Weekday number, 1-7, depending on your system's first day of the week setting)