5 Examples to Understand SQL Server DATEDIFF (Year, Month, Days)

What is SQL DATEDIFF function?

The DATEFIDFF is a function available in SQL databases like SQL Server, MySQL, etc.

In this tutorial, you will learn how to use DATEDIFF in SQL Server as there are differences in using this function in different databases.

In MS SQL Server, the DATEDIFF function is used to get the difference between two dates in terms of years, months, days, hours, minutes, etc.

For example:

SELECT DATEDIFF(day, '2018-03-13', GETDATE()) AS "Difference in days";

Syntax of DATEDIFF function

DATEDIFF(datepart, startdate, enddate);

In the syntax:

Arguments	Description
datepart	The interval or datepart argument can be year, month, day, etc. See the available list in the last section, after the examples.
startdate	The second argument is the Start Date.
enddate	Third argument is the Ending Date that you want to calculate the difference for the specified interval. In the above example, we used GETDATE() function which means using the current system date.

Learn how to use DATEDIFF in MySQL.

The example of getting years between two dates

In this example, we will get the years between two dates by using DATEDIFF SQL function.

The start date is a constant value while the end date is again the system’s current time (assume this is 2018). See the query and result:

The query with DATEDIFF:

SELECT DATEDIFF(year, '2010-03-13', GETDATE())

AS "Years Difference";

The result:

Getting the number of months difference

Similarly, you may use the month as the interval for getting the number of months between two given dates. Have a look at its usage and result by the same two dates:

SELECT DATEDIFF(month, '2010-03-13', GETDATE())

AS "Month Difference";

The result assuming the current month/year is 04/2018:

Getting days between two dates example

For the number of days, I am using two constant dates for the first and last date arguments.

For the interval argument, use the day value as follows:

SELECT DATEDIFF(day, '2017-04-13', '2018-04-13')

AS "Number of Days";

The result:

Getting days, months, and years difference with tabular data

Now see how the DATEDIFF function is used with the table column’s date column for calculating the difference in terms of days, months, and years.
For that, I am using the sto_employees table that stores information about the employees including their joining date.
By using DATEDIFF function, we will calculate the duration for each employee in the table.

See the table before and after using the DATEDIFF query:

SELECT *, GETDATE(),

DATEDIFF(day, join_date, GETDATE()) AS " Number of Days ",

DATEDIFF(month, join_date, GETDATE()) AS " Number of Months ",

DATEDIFF(year, join_date, GETDATE()) AS " Number of Years "

 FROM sto_employees;

You can see, the current date is displayed in a separate column for each employee by using the GETDATE() function.

After that, this date is subtracted from the join date to get the number of days, months, and years.

Calculating Hours, Minutes, and Seconds between two dates

Using the same table data as in the above example, we will calculate the hours, minutes, and seconds and display them with each employee’s record.

The query:

SELECT *, GETDATE() AS "Current Date",

DATEDIFF(HOUR, join_date, GETDATE()) AS " Hours ",

DATEDIFF(MINUTE, join_date, GETDATE()) AS " Minutes ",

DATEDIFF(SECOND, join_date, GETDATE()) AS " Seconds "

FROM sto_employees;

The resultset:

List of valid datepart/interval argument

The following values can be used as the datepart argument in the DATEDIFF function for getting days, years, months, weekdays, etc.

See the datepart and their abbreviation below:

Date Part	Abbreviation
Year	year, yy, yyyy
Month	month, mm, m
Day of Year	dayofyear, dy, y
Day	day, dd, d
Week	week, wk, ww
Hour	hour, hh
Minute	minute, mi, n
Second	second, ss, s
Milliseconds	milliseconds, ms

SQL DATEDIFF Function