MS Access DateDiff() Function
The DateDiff() function in MS Access calculates the difference between two dates. This is a very useful function for working with dates and performing time-based calculations or analysis.
DateDiff(): Definition and Usage
DateDiff() allows you to determine the difference between two dates in various units (years, months, days, hours, minutes, seconds). You can customize the calculation by specifying which day begins the week and how the first week of the year is defined. The function returns a numeric value representing the difference.
Syntax
Syntax
DateDiff(datepart, date1, date2, firstdayofweek, firstweekofyear)
Parameter Values
| Parameter | Description |
|---|---|
datepart |
The unit of time for the difference. Options:
|
date1, date2 |
The two dates to compare. Both are required. |
firstdayofweek (Optional) |
The first day of the week (0: system setting, 1: Sunday, 2: Monday, etc.). Defaults to Sunday (1). |
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 the first week containing Jan 1st (1). |
Examples
Calculating the Difference in Years
This calculates the difference in years between two dates.
Syntax
SELECT DateDiff("yyyy", #13/01/1998#, #09/05/2017#);
Output
19
Calculating the Difference in Months
Calculating the difference in months.
Syntax
SELECT DateDiff("m", #13/01/1998#, #09/05/2017#);
Output
234
Calculating the Difference in Days
Finding the difference in days between a date and today's date.
Syntax
SELECT DateDiff("d", #13/01/1998#, Date());
Output
(The number of days between January 13th, 1998 and today's date)