Open In App

DateDiff() and DatePart() Function in MS Access

Improve
Improve
Like Article
Like
Save
Share
Report

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 :

Parameter Description
datepart It 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 date2 It is required. The two dates to calculate the difference between date2 and date1.
firstdayofweek It 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
firstdayofyear It 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

Last Updated : 01 Oct, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads