DATEDIFF Function in SQL Server 2012

In this article, I would like to show the DATEDIFF Function in SQL Server. The DATEDIFF Function is a built-in function. The DATEDIFF Function is used to calculate the difference between two dates. So let's have a look at a practical example of how to use a DATEDIFF Function in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

The DATEDIFF Function

The SQL Server DATEDIFF Function is used to calculate the difference between two dates.

Syntax

The syntax of the DATEDIFF built-in date function is as follows :

DATEDIFF(Datepart, Startdate, Enddate)

Here, The "Datepart" parameter is the part of the datetime to calculate the difference of. The Datepart can be any of the following:

Ms         Milliseconds
Yy          Year
Qq         Quarter of the Year
Mm        Month
Dy          The Day of the Year
Dd         Day of the Month
Wk         Week
Dw         The Day of the Week
Hh          Hour
Mi          Minute
Ss           Second

Example-1

Selectdatediff(YY,'2008-06-05','2012-08-05')AsYear

Selectdatediff(Mm,'2008-06-05','2012-08-05')AsMonth

Selectdatediff(Dd,'2008-06-05','2012-08-05')AsDays

Selectdatediff(Wk,'2008-06-05','2012-08-05')AsWeek

Selectdatediff(Dw,'2008-06-05','2012-08-05')As[Day of the Week]

Selectdatediff(Dy,'2008-06-05','2012-08-05')As[Day of the Year]

Selectdatediff(Hh,'2008-06-05','2012-08-05')AsHour

Selectdatediff(Mi,'2008-06-05','2012-08-05')AsMinute

Selectdatediff(Ss,'2008-06-05','2012-08-05')AsSecond

Output

Example-2

Now you can calculate age with the DATEDIFF function:

Select getdate() as CurrentDate

Declare @CurrentDate datetime = getdate()

Declare @DOB datetime = '1986-03-15'

Select DATEDIFF( Year, @DOB, @CurrentDate) as CurrentAgeinYears

Output

Example-3

Now you can see when the second date is later than the first date. The result will be a negative number:

Select datediff(YY, '2012-08-05', '2008-06-05') As Year

Select datediff(Mm, '2012-08-05', '2008-06-05') As Month

Output