SQL DATEDIFF and DATEDIFF_BIG Functions with Sample Code

Problem

Often, we need to find the difference between time periods. Microsoft SQL Server offers functions that can be used to calculate the difference between two time periods. Read this article to learn and understand more about these SQL Server functions.

Solution

Microsoft SQL Server offers two functions which we can get the difference between two dates/times:

• DATEDIFF
• DATEDIFF_BIG

The DATEDIFF function will return the difference count between two DateTime periods with an integer value whereas the DATEDIFF_BIG function will return its output in a big integer value. Both integer (int) and big integer (bigint) are numeric data types used to store integer values. The int data type takes 4 bytes as storage size whereas bigint requires 8 bytes as storage size.

Both functions can be used with the following SQL commands: SELECT, WHERE, HAVING, GROUP BY and ORDER BY clauses in a SQL database.

The syntax of these SQL functions is outlined below:

DATEDIFF ( datepart , startdate , enddate )
DATEDIFF_BIG ( datepart , startdate , enddate )

• datepart is a portion of the date-time in which difference will be counted between the start date and end date
• startdate is the date from which you want to count the difference
• enddate is the date till which we need to count the difference

The datepart is a part of the date like it could be the year, month, date, day, hour, minutes, second, etc. It cannot be passed as a variable in the above functions. Below is the list of all possible datepart values for these functions. I have also given its abbreviations which we can use in these functions as well like the abbreviation of Year datepart is yy or yyyy so you can use any of all 3 options Year, yy, or yyyy to manipulate the year datepart. The output returned by all 3 options will be the same.

SQL DATEDIFF function

I will show you various examples using which you can understand and learn how to use this function to get the date difference. Let’s start with its basic use in the below section.

Basic use case of SQL Server DATEDIFF function

Let me find the year difference between the start date 2019/10/05 12:20:23 and end date 2021/10/05 13:23:47.

The below query has used datepart as Year and start and end date mentioned above.

SELECT DATEDIFF(YEAR, '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Years]

Once you execute the command, it will return the output as 2 because this function will find the difference between the year 2021 and 2019 as we have only specified Year as datepart so it will return the result for the year only.

If you want to find a similar difference between the start date and end date for other datepart values like month, day, week, hours, etc. then we can use these dateparts in this function in a separate statement of the same query as I have given in the following example.

SELECT DATEDIFF(YEAR,    '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Years],
       DATEDIFF(QUARTER, '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Quarters], 
       DATEDIFF(MONTH,   '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Months], 
       DATEDIFF(WEEK,    '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Weeks], 
       DATEDIFF(DAY,     '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Days], 
       DATEDIFF(HOUR,    '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Hours], 
       DATEDIFF(MINUTE,  '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Minutes], 
       DATEDIFF(SECOND,  '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Seconds] 

Have a look at its output. You can see the differences for their respective datepart (number of years, time values, etc.) in the below image.

Use DATEDIFF function with variables

The SQL DATEDIFF function also accepts variables for start date and end date values but the datepart value cannot be passed using the variable. I have taken the same dates from the above example for start date and end date and defined them as variables @startdate and @enddate in the below query. These two variables have been called out in the SQL DATEDIFF function for start date and end date values to display their result.

Each DATEDIFF statement in the below query looks the same except for a change in datepart value. Let’s run it and understand its output.

DECLARE @statetdate DATETIME2 = '2019/10/05 12:20:23', 
        @enddate DATETIME2 = '2021/10/05 13:23:47' 
SELECT DATEDIFF(YEAR,    @statetdate, @enddate) AS [Years],
       DATEDIFF(QUARTER, @statetdate, @enddate) AS [Quarters], 
       DATEDIFF(MONTH,   @statetdate, @enddate) AS [Months], 
       DATEDIFF(WEEK,    @statetdate, @enddate) AS [Weeks], 
       DATEDIFF(DAY,     @statetdate, @enddate) AS [Days], 
       DATEDIFF(HOUR,    @statetdate, @enddate) AS [Hours], 
       DATEDIFF(MINUTE,  @statetdate, @enddate) AS [Minutes], 
       DATEDIFF(SECOND,  @statetdate, @enddate) AS [Seconds] 

Below output has returned the same values as the query in the first section above.

Use DATEDIFF with other system functions

This section will explore how to use SQL DATEDIFF function with other DateTime related functions like GETDATE() or SYSDATETIME() etc. We can also use variables along with these other system functions in the DATEDIFF SQL function.

I have used multiple dateparts in the below query along with a variable @startdate as start date and GETDATE() system function as the end date to get the difference between these dates.

DECLARE @statetdate DATETIME2 = '2019/10/05 12:20:23'
SELECT DATEDIFF(YEAR,    @statetdate, GETDATE()) AS [Years],
       DATEDIFF(QUARTER, @statetdate, GETDATE()) AS [Quarters], 
       DATEDIFF(MONTH,   @statetdate, GETDATE()) AS [Months], 
       DATEDIFF(WEEK,    @statetdate, GETDATE()) AS [Weeks], 
       DATEDIFF(DAY,     @statetdate, GETDATE()) AS [Days], 
       DATEDIFF(HOUR,    @statetdate, GETDATE()) AS [Hours], 
       DATEDIFF(MINUTE,  @statetdate, GETDATE()) AS [Minutes], 
       DATEDIFF(SECOND,  @statetdate, GETDATE()) AS [Seconds] 

The above query returns similar output.

If you don’t want to use variables in the above query then you can directly pass the start date value in SQL Server function DATEDIFF as I have shown in the below query to their differences.

SELECT DATEDIFF(YEAR,    '2019/10/05 12:20:23', GETDATE()) AS [Years],
       DATEDIFF(QUARTER, '2019/10/05 12:20:23', GETDATE()) AS [Quarters], 
       DATEDIFF(MONTH,   '2019/10/05 12:20:23', GETDATE()) AS [Months], 
       DATEDIFF(WEEK,    '2019/10/05 12:20:23', GETDATE()) AS [Weeks], 
       DATEDIFF(DAY,     '2019/10/05 12:20:23', GETDATE()) AS [Days], 
       DATEDIFF(HOUR,    '2019/10/05 12:20:23', GETDATE()) AS [Hours], 
       DATEDIFF(MINUTE,  '2019/10/05 12:20:23', GETDATE()) AS [Minutes], 
       DATEDIFF(SECOND,  '2019/10/05 12:20:23', GETDATE()) AS [Seconds] 

Have a look at the below output.

Use DATEDIFF with subqueries

We can also use subqueries in the SQL DATEDIFF function. Let’s assume you have a table named OrderDetails. This table stores information about each order company sells. Now, you want to find the date and time difference between the first order date and the last order date then you can use other SQL functions MIN and MAX on column Date which stores the date of order along with this SQL DATEDIFF function.

I have displayed LastOrderDate using SQL MAX function, FirstOrderDate using MIN function, and the date and time differences between these two order dates using various dateparts.

SELECT (SELECT MAX(DATE) FROM OrderDetails AS [LastOrderDate], 
       (SELECT MIN(DATE) FROM OrderDetails AS [FirstOrderDate], 
       DATEDIFF(YEAR,    (SELECT MAX(DATE) FROM OrderDetails, SELECT MIN(DATE) FROM OrderDetails) AS [Years], 
       DATEDIFF(QUARTER, (SELECT MAX(DATE) FROM OrderDetails, SELECT MIN(DATE) FROM OrderDetails) AS [Quarters], 
       DATEDIFF(MONTH,   (SELECT MAX(DATE) FROM OrderDetails, SELECT MIN(DATE) FROM OrderDetails) AS [Months], 
       DATEDIFF(WEEK,    (SELECT MAX(DATE) FROM OrderDetails, SELECT MIN(DATE) FROM OrderDetails) AS [Weeks], 
       DATEDIFF(DAY,     (SELECT MAX(DATE) FROM OrderDetails, SELECT MIN(DATE) FROM OrderDetails) AS [Days], 
       DATEDIFF(HOUR,    (SELECT MAX(DATE) FROM OrderDetails, SELECT MIN(DATE) FROM OrderDetails) AS [Hours], 
       DATEDIFF(MINUTE,  (SELECT MAX(DATE) FROM OrderDetails, SELECT MIN(DATE) FROM OrderDetails) AS [Minutes], 
       DATEDIFF(SECOND,  (SELECT MAX(DATE) FROM OrderDetails, SELECT MIN(DATE) FROM OrderDetails) AS [Seconds] 
GO
SELECT (SELECT MAX(DATE) FROM OrderDetails AS [LastOrderDate], 
       (SELECT MIN(DATE) FROM OrderDetails AS [FirstOrderDate], 
       DATEDIFF(YEAR,    (SELECT MIN(DATE) FROM OrderDetails, SELECT MAX(DATE) FROM OrderDetails) AS [Years], 
       DATEDIFF(QUARTER, (SELECT MIN(DATE) FROM OrderDetails, SELECT MAX(DATE) FROM OrderDetails) AS [Quarters], 
       DATEDIFF(MONTH,   (SELECT MIN(DATE) FROM OrderDetails, SELECT MAX(DATE) FROM OrderDetails) AS [Months], 
       DATEDIFF(WEEK,    (SELECT MIN(DATE) FROM OrderDetails, SELECT MAX(DATE) FROM OrderDetails) AS [Weeks], 
       DATEDIFF(DAY,     (SELECT MIN(DATE) FROM OrderDetails, SELECT MAX(DATE) FROM OrderDetails) AS [Days], 
       DATEDIFF(HOUR,    (SELECT MIN(DATE) FROM OrderDetails, SELECT MAX(DATE) FROM OrderDetails) AS [Hours], 
       DATEDIFF(MINUTE,  (SELECT MIN(DATE) FROM OrderDetails, SELECT MAX(DATE) FROM OrderDetails) AS [Minutes], 
       DATEDIFF(SECOND,  (SELECT MIN(DATE) FROM OrderDetails, SELECT MAX(DATE) FROM OrderDetails) AS [Seconds] 
GO

There are 2 sets of queries I have executed in the below image. I have swapped the start date and end date in the second query to understand its impact on output. We can see the output is the same for both queries but with one difference that it has a negative sign.

This function will return a negative value if the start date is greater than the end date value. I have passed the start date as last order date and end date as first order date in the first set of the query in the below image whereas I have passed start date as first order date and end date as last order date in the second set query to show you the difference.

Use DATEDIFF with Columns

This section will explore how to call a column in the SQL DATEDIFF function. Let’s assume you are storing date value in a column and now you want to find the difference between the stored value of date and current date time. We can use the column name in this function along with the GETDATE function to get the difference.

Here I have used the stored date value in the column as the start date and the GETDATE system function as the end date to get the difference.

SELECT TOP 10
       Product, 
       Date AS [OrderDate], 
       DATEDIFF(YEAR,    Date, GETDATE()) AS [Years], 
       DATEDIFF(QUARTER, Date, GETDATE()) AS [Quarters], 
       DATEDIFF(MONTH,   Date, GETDATE()) AS [Months], 
       DATEDIFF(WEEK,    Date, GETDATE()) AS [Weeks], 
       DATEDIFF(DAY,     Date, GETDATE()) AS [Days], 
       DATEDIFF(HOUR,    Date, GETDATE()) AS [Hours], 
       DATEDIFF(MINUTE,  Date, GETDATE()) AS [Minutes], 
       DATEDIFF(SECOND,  Date, GETDATE()) AS [Seconds] 
FROM OrderDetails

Let’s see the output where we can see each product, the order date, and the DateTime difference since it was sold to the customer.

SQL DATEDIFF_BIG Function

The SQL DATEDIFF_BIG is another system function to get the DateTime difference between two specified dateparts. The output returned by this function is a bigint value. The int data type takes 4 bytes of storage whereas bigint requires 8 bytes as storage size.

You can just replace the DATEDIFF function with DATEDIFF_BIG in all the above use cases to get its result. Let me show you few use cases of the DATEDIFF_BIG function in the below sections. The output will return the same and there will not be any difference from an output standpoint. The only difference is DATEDIFF will return output an int value whereas DATEDIFF_BIG will return a bigint value.

Basic use case of SQL Server DATEDIFF_BIG Function

I have copied the same query which I used for the SQL DATEDIFF function from the basic use section and replaced the DATEDIFF function with the DATEDIFF_BIG function as shown below.

SELECT DATEDIFF_BIG(YEAR,    '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Years],
       DATEDIFF_BIG(QUARTER, '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Quarters], 
       DATEDIFF_BIG(MONTH,   '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Months], 
       DATEDIFF_BIG(WEEK,    '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Weeks], 
       DATEDIFF_BIG(DAY,     '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Days], 
       DATEDIFF_BIG(HOUR,    '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Hours], 
       DATEDIFF_BIG(MINUTE,  '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Minutes], 
       DATEDIFF_BIG(SECOND,  '2019/10/05 12:20:23', '2021/10/05 13:23:47') AS [Seconds] 

You can analyze the output and see it shows the same output as the DATEDIFF function.

Use DATEDIFF_BIG function with variables

We can also use variables \ parameters for start date and end date in SQL DATEDIFF_BIG function as shown in the below query. I have defined two dates in @startdate and @enddate variables and used these variables in SQL function DATEDIFF_BIG.

DECLARE @statetdate DATETIME2 = '2019/10/05 12:20:23', 
        @enddate DATETIME2 = '2021/10/05 13:23:47' 
SELECT DATEDIFF_BIG(YEAR,    @statetdate, @enddate) AS [Years],
       DATEDIFF_BIG(QUARTER, @statetdate, @enddate) AS [Quarters], 
       DATEDIFF_BIG(MONTH,   @statetdate, @enddate) AS [Months], 
       DATEDIFF_BIG(WEEK,    @statetdate, @enddate) AS [Weeks], 
       DATEDIFF_BIG(DAY,     @statetdate, @enddate) AS [Days], 
       DATEDIFF_BIG(HOUR,    @statetdate, @enddate) AS [Hours], 
       DATEDIFF_BIG(MINUTE,  @statetdate, @enddate) AS [Minutes], 
       DATEDIFF_BIG(SECOND,  @statetdate, @enddate) AS [Seconds] 

Have a look at the output of above query in the below image.

If you want to practice and understand the use cases of DATEDIFF_BIG then you can replace the DATEDIFF function used in the above examples for each respective use case. This will be useful when you have very large values for the output which exceed the maximum value for an int datatype.

Next Steps

Check out these other resources:

• SQL Server Date Function Tutorial
• SQL DATEDIFF Function
• More SQL Server Date Related Articles
• DATEDIFF SQL Server Function
• Add and Subtract Dates using DATEADD in SQL Server
• SQL Server DATEADD Function
• SQL Server, Oracle and PostgreSQL Date Functions
• Using Window Functions to Create Smart, Quick Queries
• SQL Server T-SQL Window Functions Tutorial
• SQL Server Window Aggregate Functions SUM, MIN, MAX and AVG
• SQL Window Functions in SQL Server, Oracle and PostgreSQL