SQL DATEDIFF Function

A visual guide to mastering date calculations with DATEDIFF function. Explore days, months, and years differences for effective time-based analysis.

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:

SQL DATEDIFF Year

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:

SQL DATEDIFF Months

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:

SQL DATEDIFF days

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;

SQL DATEDIFF table

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:

SQL DATEDIFF hours

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

 

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!