SQL Server DATEDIFF: An Intro to Calculating Time Intervals

By Community / Use Cases
Feb 09, 2023

Hey there! Have you ever needed to calculate the difference between two dates in your SQL database? If so, I’ve got some good news for you: SQL Server has a fantastic built-in function called DATEDIFF that does just that. In this post, we’ll explore what DATEDIFF is, how it works, and when and why to use it. We’ll also dive into some practical examples, like finding the number of days between a date and today and getting the date difference between two columns.

So, let’s dive in!

DATEDIFF: What is it?

DATEDIFF is a powerful SQL Server function that calculates the difference between two dates or datetimes, returning the result as an integer. It’s super helpful when you need to find the age of something, like how many days old a user account is or the number of months between two events.

DATEDIFF: Syntax and parameters

The syntax for DATEDIFF is pretty straightforward:

DATEDIFF(datepart, startdate, enddate)

Let’s explore the parameters used here:

• datepart: The unit of time you want to use for the calculation, like year, quarter, month, day, or even smaller units like hour, minute, or second.

• startdate: The first date or datetime value.

• enddate: The second date or datetime value.

Keep in mind that DATEDIFF only returns a positive integer, so if the end date is earlier than the start date, you’ll get a negative result.

How DATEDIFF works

When using DATEDIFF, it’s essential to understand its behavior. DATEDIFF calculates the number of specified datepart boundaries crossed between startdate and enddate. However, it doesn’t count the number of datepart units between the two dates. Instead, it looks at the boundaries between the units. For example, consider the following query:

SELECT DATEDIFF(month, '2022-01-29', '2022-02-01');

Although there are only three days between the two dates, the result is 1 because the month boundary (from January to February) is crossed once.

DATEDIFF accuracy

DATEDIFF returns an integer, which can lead to truncation or rounding issues. For instance, if you calculate the difference in months between two dates and the result is not a whole number, DATEDIFF will truncate the fractional part. For example:

SELECT DATEDIFF(month, '2022-01-15', '2022-02-14');

The actual difference is about 0.97 months, but DATEDIFF returns 0 because the month boundary isn’t crossed. To achieve higher accuracy, you can use a smaller datepart and convert the result to your desired unit. For instance, you can calculate the difference in days and then divide it by the average number of days in a month:

SELECT DATEDIFF(day, '2022-01-15', '2022-02-14') / 30.44;

Keep in mind that this is just an approximation.

DATEDIFF and boundary values

Understanding how DATEDIFF handles boundary values is crucial for correct calculations. For example, let’s calculate the difference in years between December 31, 2021, and January 1, 2022:

SELECT DATEDIFF(year, '2021-12-31', '2022-01-01');

Although there’s only a one-day gap between the dates, the result is 1 because the year boundary is crossed. Similarly, calculating the difference in months between January 31, 2022, and February 1, 2022, would yield 1, despite the one-day gap. It’s essential to consider these boundary cases and adjust your calculations accordingly, depending on your specific requirements.

When and why to use DATEDIFF

You might be wondering when you’d actually use DATEDIFF in real-world scenarios. Let’s see some examples:

• Calculate the age of a user or product in days, months, or years.

• Find the number of days until an event or deadline.

• Analyze the time between two events, like user logins or purchases.

Now that we’ve covered the basics, let’s look at some practical examples.

Example 1: Calculating the Number of Days Between a Date and Today

Suppose you want to find out how many days have passed since a user signed up. Here’s how you can do that using DATEDIFF:

SELECT DATEDIFF(day, signup_date, GETDATE()) AS days_since_signup

FROM users;

In this example, we calculate the difference between the signup date and the current date (using the GETDATE() function) in days.

Example 2: Getting the Date Difference Between Two Columns

Imagine you have a table with two date columns, start_date, and end_date, and you want to calculate the number of days between them. Here’s how:

SELECT DATEDIFF(day, start_date, end_date) AS duration

FROM events;

In this case, we’re finding the difference between the start_date and end_date columns in days.

SELECT DATEDIFF

Sometimes, you might want to filter your query results based on the output of DATEDIFF. For example, let’s say you want to display only the events that lasted more than seven days:

SELECT *

FROM events

WHERE DATEDIFF(day, start_date, end_date) > 7;

In this query, we’re using DATEDIFF in the WHERE clause to filter the results based on the difference between the start_date and end_date columns.

DATEADD: The Companion Function

Another useful function in SQL Server is DATEADD, which allows you to add or subtract a specified time interval from a given date or datetime value. The syntax is as follows:

DATEADD(datepart, number, date)

Let’s understand the parameters:

• datepart: The unit of time you want to add or subtract, like year, quarter, month, day, hour, minute, or second.

• number: The number of units to add or subtract. Use a positive integer to add time and a negative integer to subtract time.

• date: The date or datetime value you want to modify.

For example, if you want to find the date 30 days after a user’s signup date, you can use the following:

SELECT DATEADD(day, 30, signup_date) AS thirty_days_later

FROM users;

DATEDIFF in Snowflake, MySQL, and PostgreSQL

Although DATEDIFF is specific to SQL Server, similar functions are available in other database systems:

Snowflake

Snowflake’s DATEDIFF function has the same syntax as SQL Server:

SELECT DATEDIFF(datepart, startdate, enddate)

FROM table_name;

However, there are some differences in the supported datepart values. For example, Snowflake supports the following values: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, and NANOSECOND. For example, to calculate the difference between two dates in weeks, you would use:

SELECT DATEDIFF(week, '2022-01-01', '2022-01-15')

FROM table_name;

MySQL

MySQL uses the TIMESTAMPDIFF function to calculate the difference between two dates or datetimes:

SELECT TIMESTAMPDIFF(unit, startdate, enddate)

FROM table_name;

Where unit represents the unit of time, like YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND. Note that TIMESTAMPDIFF returns a signed integer value, and the order of the startdate and enddate parameters matters. Therefore, if you swap the order, the result will have the opposite sign. Here’s an example of calculating the difference between two dates in hours:

SELECT TIMESTAMPDIFF(HOUR, '2022-01-01 08:00:00', '2022-01-01 12:00:00')

FROM table_name;

PostgreSQL

PostgreSQL allows you to calculate the difference between two dates or timestamps directly by subtracting one from the other:

SELECT end_date - start_date

FROM table_name;

This subtraction returns an interval value. To extract a specific unit from the interval, you can use the EXTRACT function:

SELECT EXTRACT(unit FROM interval)

FROM table_name;

Here's an example of calculating the difference between two dates in months:

SELECT EXTRACT(MONTH FROM (end_date - start_date))

FROM table_name;

It’s important to note that the result may be something other than an integer when using EXTRACT, mainly when calculating the difference in months or years. However, you can use the ::integer type cast or the floor() function to convert the result to an integer.

Wrapping Up

And that’s a wrap! You now know the basics of DATEDIFF, its syntax and parameters, and how it works. We’ve also looked at some practical examples, like calculating the number of days between a date and today and getting the date difference between two columns. Additionally, we’ve touched on the SELECT DATEDIFF, DATEADD, and the equivalents in Snowflake, MySQL, and PostgreSQL.

Equipped with this knowledge, you can now confidently use DATEDIFF and its counterparts to analyze and manipulate date and time intervals in your SQL queries.

Happy querying!

About the author

This post was written by Juan Reyes. As an entrepreneur, skilled engineer, and mental health champion, Juan pursues sustainable self-growth, embodying leadership, wit, and passion. With over 15 years of experience in the tech industry, Juan has had the opportunity to work with some of the most prominent players in mobile development, web development, and e-commerce in Japan and the US.