MS Access DateDiff function
DateDiff function can be used in both Access queries and VBA code. It evaluates two date/time
values and returns the difference for a specific interval of the two date/time values.
Click the link below to see examples of how to use DateDiff function.
Access DateDiff function examples
DateDiff function syntax:
There are three forms of DateDiff function that can be used.
- DateDiff (interval, date1, date2)
- DateDiff (interval, date1, date2, firstdayofweek)
- DateDiff (interval, date1, date2, firstdayofweek, firstweekofyear)
Function Parameters:
| Parameter |
Required/Optional |
Description |
| interval |
Required |
Interval is the abbreviation for a specific part of a date/time value you want to retrieve.
The interval can be Year, Quarter, Month, Day of year, Day, Weekday, Week, Hour, Minute, Second.
The interval parameter settings:
| Setting |
Description |
| yyyy |
Year |
| q |
Quarter |
| m |
Month |
| y |
Day of year |
| d |
Day |
| w |
Weekday |
| ww |
Week |
| h |
Hour |
| n |
Minute |
| s |
Second |
|
| date1, date2 |
Required |
These two parameters are the two dates or time that need to be compared and calculated for date/time difference.
The values can be field names with a Date/Time data type, variables of date data type in VBA, or date literals.
A variable is an unknown value at design time but will be dynamically assigned a specific value
at program run-time.
Date literal is a known value of any sequence of characters with a valid format that is surrounded by number signs (#)
such as such as #7-Nov-93#. Valid formats include the date format specified by the date locale settings
(in Control Panel's Regional Options) for your code or the universal date format.
For example, #12/31/92# is the date literal that represents December 31, 1992, where English-U.S. is
the locale setting for your application. Use date literals to maximize portability across national languages.
|
| firstdayofweek |
Optional |
A constant that specifies the first day of the week. Because this parameter is optional,
you don't have to specify it in the function. If not specified, Sunday is assumed to be the first
day of the week.
The firstdayofweek argument affects DateDiff calculations that return Weekday part of
the date value evaluated. The corresponding interval symbol for Weekday is "w" (without quotes).
The firstdayofweek argument has these settings. Note: Constants listed here can be used
in VBA code to replace the actual values.
| Constant |
Value |
Description |
| 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 |
Optional |
A constant that specifies the first week of the year. Because this parameter is optional,
you don't have to specify it in the function. If not specified, the first week is
assumed to be the week in which January 1 occurs.
The firstweekofyear argument affects DateDiff calculations that return Week part of the date
value evaluated. The corresponding interval symbol for Week is "ww" (without quotes).
The firstweekofyear argument has these settings. Note: Constants listed here can be used
in VBA code to replace the actual values.
| Constant |
Value |
Description |
| vbUseSystem |
0 |
Use the NLS API setting. |
| vbFirstJan1 |
1 |
Start with week in which January 1 occurs
(default). |
| vbFirstFourDays |
2 |
Start with the first week that has at least four
days in the new year. |
| vbFirstFullWeek |
3 |
Start with first full week of the year. |
|
Happy Coding!
Copyright© GeeksEngine.com
| Other Recent Articles from the MS Access category:
|
