Skip to content
Related Articles

Related Articles

DateDiff() and DatePart() Function in MS Access

Improve Article
Save Article
Like Article
  • Last Updated : 01 Oct, 2020

In this article, we are going to cover DateDiff() and DatePart function with examples. DateDiff() is used to calculate the difference between two dates. DatePart is used when you want a specific part want to display from date specified format. Let’s discuss one by one.

1. DateDiff() Function :
In MS Access, the DateDiff() function returns the difference between two dates. In this function, it will take the first parameter datepart, and the second parameter will be date1 and the third parameter will be date2. And it will return the difference between two dates.

Syntax :

DateDiff(datepart, date1, date2, firstdayofweek, firstweekofyear)

Parameter Values :

ParameterDescription
datepartIt is required. The part to return.
yyyy = Year
q = Quarter
m = month
y = Day of the year
d = Day
w = Weekday
ww = Week
h = hour
n = Minute
s = Second
date1 and date2It is required. The two dates to calculate the difference between date2 and date1.
firstdayofweekIt is optional. It will specifies the first day of the week.
0 = Use the NLS API setting
1 = Sunday (this is default)
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday
firstdayofyearIt is ptional.It will  specifies the first week of the year.
0 = Use the NLS API setting
1 = It indicate the first week that includes Jan 1st (by default)
2 = It represents first week in the year.(At least 4 days)
3 = It Uses the first full week of the year.

Example-1 :

SELECT DateDiff("yyyy", #15/02/2000#, #06/05/2020#);

Output :

20

Example-2 :

SELECT DateDiff("m", #15/02/2000#, #06/05/2020#);

Output :

244

2. DatePart() Function :
In MS Access, the DatePart() function returns the specified part of a date. In this function the first parameter will be the specified datepart symbol and the second parameter will be the date. And it will return the date part of the date.

Syntax :

DatePart(datepart, date, firstdayofweek, firstweekofyear);

Parameter Values :
In this parameter values the same as Datadiff() function for datepart, firstdayofweek, and firstweekofyear. For data value, It is required it specifies the date.

Example-1 :

SELECT DatePart("yyyy", #02/09/2020#);

Output :

2020

Example-2 :

SELECT DatePart("m", #02/09/2020#);

Output :

2
My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!