Understanding the DATEDIFF Function in SQL Server

Published in

CodeX

2 min readJun 26, 2024

The DATEDIFF function in SQL Server is a powerful tool used to calculate the difference between two dates. It is widely used in database management and data analysis to measure the time interval between two specific dates. This article will delve into the syntax, usage, and practical examples of the DATEDIFF function.

What is the DATEDIFF Function?

The DATEDIFF function returns the count of specified datepart boundaries crossed between the specified startdate and enddate. The function is particularly useful for calculating age, duration, intervals, and periods.

Syntax of DATEDIFF

The basic syntax of the DATEDIFF function is as follows:

DATEDIFF(datepart, startdate, enddate)

datepart: This parameter specifies the part of the date to return the difference. Common dateparts include year, quarter, month, day, hour, minute, second, etc.
startdate: The beginning date of the interval.
enddate: The ending date of the interval.

Common Dateparts

Here are some common dateparts used with DATEDIFF:

year (or yy, yyyy): Year difference
quarter (or qq, q): Quarter difference
month (or mm, m): Month difference
day (or dd, d): Day difference
hour (or hh): Hour difference
minute (or mi, n): Minute difference
second (or ss, s): Second difference

Practical Examples

Let’s explore some practical examples to understand how DATEDIFF works.

Example 1: Calculating the Difference in Days

Consider the following example where we want to calculate the number of days between two dates:

SELECT DATEDIFF(day, '2023-01-01', '2023-12-31') AS DaysDifference;

This query returns the number of days between January 1, 2023, and December 31, 2023. The result is 364 days.

Example 2: Calculating the Difference in Months

In this example, we calculate the difference in months between two dates:

SELECT DATEDIFF(month, '2023-01-01', '2023-12-31') AS MonthsDifference;

This query returns the number of months between January 1, 2023, and December 31, 2023. The result is 11 months.

Example 3: Calculating the Difference in Years

Let’s calculate the difference in years between two dates:

SELECT DATEDIFF(year, '2020-01-01', '2023-01-01') AS YearsDifference;

This query returns the number of years between January 1, 2020, and January 1, 2023. The result is 3 years.

Example 4: Calculating Age

The DATEDIFF function is often used to calculate age. Here’s an example:

DECLARE @BirthDate DATE = '1990-06-15';
SELECT DATEDIFF(year, @BirthDate, GETDATE()) AS Age;

This query calculates the age of a person born on June 15, 1990, as of the current date.

Conclusion

The DATEDIFF function is a versatile tool in SQL Server for calculating the difference between two dates. Whether you need to compute the number of days, months, years, or any other time interval between dates, DATEDIFF provides a simple and efficient way to perform these calculations. By understanding and utilizing the DATEDIFF function, you can enhance your data analysis and reporting capabilities in SQL Server.