DATEDIFF (Transact-SQL) - SQL Server (2023)

  • Article

Applies to: DATEDIFF (Transact-SQL) - SQL Server (1) SQL Server DATEDIFF (Transact-SQL) - SQL Server (2) Azure SQL Database DATEDIFF (Transact-SQL) - SQL Server (3) Azure SQL Managed Instance DATEDIFF (Transact-SQL) - SQL Server (4) Azure Synapse Analytics DATEDIFF (Transact-SQL) - SQL Server (5) Analytics Platform System (PDW)

This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

See DATEDIFF_BIG (Transact-SQL) for a function that handles larger differences between the startdate and enddate values. See Date and Time Data Types and Functions (Transact-SQL) for an overview of all Transact-SQL date and time data types and functions.

DATEDIFF (Transact-SQL) - SQL Server (6) Transact-SQL syntax conventions

Syntax

DATEDIFF ( datepart , startdate , enddate ) 

Note

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

datepart
The units in which DATEDIFF reports the difference between the startdate and enddate. Commonly used datepart units include month or second.

(Video) 57. DATEDIFF Function in SQL

The datepart value cannot be specified in a variable, nor as a quoted string like 'month'.

The following table lists all the valid datepart values. DATEDIFF accepts either the full name of the datepart, or any listed abbreviation of the full name.

datepart namedatepart abbreviation
yeary, yy, yyyy
quarterqq, q
monthmm, m
dayofyeardy
daydd, d
weekwk, ww
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns

Note

Each specific datepart name and abbreviations for that datepart name will return the same value.

startdate
An expression that can resolve to one of the following values:

  • date
  • datetime
  • datetimeoffset
  • datetime2
  • smalldatetime
  • time

Use four-digit years to avoid ambiguity. See Configure the two digit year cutoff Server Configuration Option for information about two-digit year values.

enddate
See startdate.

Return Type

int

Return Value

The int difference between the startdate and enddate, expressed in the boundary set by datepart.

(Video) Using the TSQL DateDiff Function

For example, SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); returns -2, hinting that 2036 must be a leap year. This case means that if we start at startdate '2036-03-01', and then count -2 days, we reach the enddate of '2036-02-28'.

For a return value out of range for int (-2,147,483,648 to +2,147,483,647), DATEDIFF returns an error. For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds.

If startdate and enddate are both assigned only a time value, and the datepart is not a time datepart, DATEDIFF returns 0.

DATEDIFF uses the time zone offset component of startdate or enddate to calculate the return value.

Because smalldatetime is accurate only to the minute, seconds and milliseconds are always set to 0 in the return value when startdate or enddate have a smalldatetime value.

If only a time value is assigned to a date data type variable, DATEDIFF sets the value of the missing date part to the default value: 1900-01-01. If only a date value is assigned to a variable of a time or date data type, DATEDIFF sets the value of the missing time part to the default value: 00:00:00. If either startdate or enddate have only a time part and the other only a date part, DATEDIFF sets the missing time and date parts to the default values.

If startdate and enddate have different date data types, and one has more time parts or fractional seconds precision than the other, DATEDIFF sets the missing parts of the other to 0.

datepart boundaries

The following statements have the same startdate and the same enddate values. Those dates are adjacent and they differ in time by a hundred nanoseconds (.0000001 second). The difference between the startdate and enddate in each statement crosses one calendar or time boundary of its datepart. Each statement returns 1.

SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');SELECT DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

If startdate and enddate have different year values, but they have the same calendar week values, DATEDIFF will return 0 for datepart week.

Remarks

Use DATEDIFF in the SELECT <list>, WHERE, HAVING, GROUP BY and ORDER BY clauses.

(Video) What is the difference between two dates using the DATEDIFF function

DATEDIFF implicitly casts string literals as a datetime2 type. This means that DATEDIFF does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.

DATEDIFF may overflow with a precision of minute or higher if the difference between enddate and startdate returns a value that is out of range for int.

Examples

These examples use different types of expressions as arguments for the startdate and enddate parameters.

A. Specifying columns for startdate and enddate

This example calculates the number of day boundaries crossed between dates in two columns in a table.

CREATE TABLE dbo.Duration (startDate datetime2, endDate datetime2); INSERT INTO dbo.Duration(startDate, endDate) VALUES ('2007-05-06 12:10:09', '2007-05-07 12:10:09'); SELECT DATEDIFF(day, startDate, endDate) AS 'Duration' FROM dbo.Duration; -- Returns: 1 

B. Specifying user-defined variables for startdate and enddate

In this example, user-defined variables serve as arguments for startdate and enddate.

DECLARE @startdate DATETIME2 = '2007-05-05 12:10:09.3312722'; DECLARE @enddate DATETIME2 = '2007-05-04 12:10:09.3312722'; SELECT DATEDIFF(day, @startdate, @enddate); 

C. Specifying scalar system functions for startdate and enddate

This example uses scalar system functions as arguments for startdate and enddate.

SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME()); 

D. Specifying scalar subqueries and scalar functions for startdate and enddate

This example uses scalar subqueries and scalar functions as arguments for startdate and enddate.

USE AdventureWorks2012; GO SELECT DATEDIFF(day, (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader), (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)); 

E. Specifying constants for startdate and enddate

This example uses character constants as arguments for startdate and enddate.

SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', '2007-05-08 09:53:01.0376635'); 

F. Specifying numeric expressions and scalar system functions for enddate

This example uses a numeric expression, (GETDATE() + 1), and scalar system functions GETDATE and SYSDATETIME, as arguments for enddate.

(Video) SQL Server Date Functions: GETDATE() and DATEDIFF()

USE AdventureWorks2012; GO SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE() + 1) AS NumberOfDays FROM Sales.SalesOrderHeader; GO USE AdventureWorks2012; GO SELECT DATEDIFF( day, '2007-05-07 09:53:01.0376635', DATEADD(day, 1, SYSDATETIME()) ) AS NumberOfDays FROM Sales.SalesOrderHeader; GO 

G. Specifying ranking functions for startdate

This example uses a ranking function as an argument for startdate.

USE AdventureWorks2012; GO SELECT p.FirstName, p.LastName ,DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY a.PostalCode), SYSDATETIME()) AS 'Row Number' FROM Sales.SalesPerson s INNER JOIN Person.Person p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0; 

H. Specifying an aggregate window function for startdate

This example uses an aggregate window function as an argument for startdate.

USE AdventureWorks2012; GO SELECT soh.SalesOrderID, sod.ProductID, sod.OrderQty, soh.OrderDate, DATEDIFF(day, MIN(soh.OrderDate) OVER(PARTITION BY soh.SalesOrderID), SYSDATETIME()) AS 'Total' FROM Sales.SalesOrderDetail sod INNER JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID WHERE soh.SalesOrderID IN(43659, 58918); GO 

I. Finding difference between startdate and enddate as date parts strings

-- DOES NOT ACCOUNT FOR LEAP YEARSDECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100);DECLARE @years INT, @months INT, @days INT, @hours INT, @minutes INT, @seconds INT, @milliseconds INT;SET @date1 = '1900-01-01 00:00:00.000'SET @date2 = '2018-12-12 07:08:01.123'SELECT @years = DATEDIFF(yy, @date1, @date2)IF DATEADD(yy, -@years, @date2) < @date1 SELECT @years = @years-1SET @date2 = DATEADD(yy, -@years, @date2)SELECT @months = DATEDIFF(mm, @date1, @date2)IF DATEADD(mm, -@months, @date2) < @date1 SELECT @months=@months-1SET @date2= DATEADD(mm, -@months, @date2)SELECT @days=DATEDIFF(dd, @date1, @date2)IF DATEADD(dd, -@days, @date2) < @date1 SELECT @days=@days-1SET @date2= DATEADD(dd, -@days, @date2)SELECT @hours=DATEDIFF(hh, @date1, @date2)IF DATEADD(hh, -@hours, @date2) < @date1 SELECT @hours=@hours-1SET @date2= DATEADD(hh, -@hours, @date2)SELECT @minutes=DATEDIFF(mi, @date1, @date2)IF DATEADD(mi, -@minutes, @date2) < @date1 SELECT @minutes=@minutes-1SET @date2= DATEADD(mi, -@minutes, @date2)SELECT @seconds=DATEDIFF(s, @date1, @date2)IF DATEADD(s, -@seconds, @date2) < @date1 SELECT @seconds=@seconds-1SET @date2= DATEADD(s, -@seconds, @date2)SELECT @milliseconds=DATEDIFF(ms, @date1, @date2)SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','') + ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','') + ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','') + ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','') + ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','') + ISNULL(' ' + CAST(@seconds AS VARCHAR(10)) + CASE WHEN @milliseconds > 0 THEN '.' + CAST(@milliseconds AS VARCHAR(10)) ELSE '' END + ' seconds','')SELECT @result

Here is the result set.

118 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

These examples use different types of expressions as arguments for the startdate and enddate parameters.

J. Specifying columns for startdate and enddate

This example calculates the number of day boundaries crossed between dates in two columns in a table.

CREATE TABLE dbo.Duration (startDate datetime2, endDate datetime2); INSERT INTO dbo.Duration (startDate, endDate) VALUES ('2007-05-06 12:10:09', '2007-05-07 12:10:09'); SELECT TOP(1) DATEDIFF(day, startDate, endDate) AS Duration FROM dbo.Duration; -- Returns: 1 

K. Specifying scalar subqueries and scalar functions for startdate and enddate

This example uses scalar subqueries and scalar functions as arguments for startdate and enddate.

-- Uses AdventureWorks SELECT TOP(1) DATEDIFF(day, (SELECT MIN(HireDate) FROM dbo.DimEmployee), (SELECT MAX(HireDate) FROM dbo.DimEmployee)) FROM dbo.DimEmployee; 

L. Specifying constants for startdate and enddate

This example uses character constants as arguments for startdate and enddate.

-- Uses AdventureWorks SELECT TOP(1) DATEDIFF(day, '2007-05-07 09:53:01.0376635', '2007-05-08 09:53:01.0376635') FROM DimCustomer; 

M. Specifying ranking functions for startdate

This example uses a ranking function as an argument for startdate.

-- Uses AdventureWorks SELECT FirstName, LastName, DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY DepartmentName), SYSDATETIME()) AS RowNumber FROM dbo.DimEmployee; 

N. Specifying an aggregate window function for startdate

This example uses an aggregate window function as an argument for startdate.

-- Uses AdventureWorks SELECT FirstName, LastName, DepartmentName, DATEDIFF(year, MAX(HireDate) OVER (PARTITION BY DepartmentName), SYSDATETIME()) AS SomeValue FROM dbo.DimEmployee 

See also

DATEDIFF_BIG (Transact-SQL)
CAST and CONVERT (Transact-SQL)

(Video) DateTime functions in SQL Server Part 25

FAQs

DATEDIFF (Transact-SQL) - SQL Server? ›

DATEDIFF uses the time zone offset component of startdate or enddate to calculate the return value. Because smalldatetime is accurate only to the minute, seconds and milliseconds are always set to 0 in the return value when startdate or enddate have a smalldatetime value.

How to transact SQL difference between two dates? ›

To calculate the difference between the arrival and the departure in T-SQL, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument can be microsecond , second , minute , hour , day , week , month , quarter , or year . Here, you'd like to get the difference in seconds, so choose second.

How to calculate days between two dates in T-SQL? ›

To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.

How to get date in transact SQL? ›

T-SQL - Date Functions
  • Select getdate() as currentdatetime.
  • Select datepart(day, getdate()) as currentdate.
  • Select datepart(month, getdate()) as currentmonth.
  • Select dateadd(day, 10, getdate()) as after10daysdatetimefromcurrentdatetime.

How to get day from date in transact SQL? ›

If you want to get a day from a date in a table, use the SQL Server DAY() function. This function takes only one argument – the date. This can be a date or date and time data type. (In our example, the column VisitDate is of the date data type.)

Can Datediff be used in a where clause? ›

Use DATEDIFF in the SELECT <list> , WHERE , HAVING , GROUP BY and ORDER BY clauses.

What are the three arguments for Datediff? ›

The DATEDIFF function accepts three parameters, interval, date1, and date2. This function returns an integer representing the difference between date1 and date2 with the unit specified by the interval.

How to calculate working days between two dates in SQL Server? ›

The statement DATEDIFF(dd,@fromdate,@todate) + 1 gives the number of dates between the two dates. The statement DATEDIFF(wk,@fromdate,@todate) gives the number of weeks between dates and * 2 gives us the weekend (Saturday and Sunday) count. The next two statements excludes the day if it's a Saturday or Sunday.

How do I calculate days from two dates? ›

How do I go about calculating the days between two dates? To calculate the number of days between two dates, you need to subtract the start date from the end date.

How to set date in tsql? ›

To update a date field with T-SQL, here is the general syntax: UPDATE table_name SET date_field = 'date_value' [WHERE conditions]; To update with the current date: UPDATE table_name SET date_field = getdate();

How to convert date to datetime in transact SQL? ›

We can convert the Date into Datetime in two ways.
  • Using CONVERT() function: Convert means to change the form or value of something. ...
  • Using the CAST() function: SQL Server uses the CAST() function to cast or convert a value or an expression from one data type to another.
Oct 18, 2021

How to get month in transact SQL? ›

Discussion: To get a month from a date field in SQL Server, use the MONTH() function. This function takes only one argument – the date.

What is datepart in SQL? ›

In SQL Server, the DATEPART() function returns an integer representing the specific part of the given date e.g. day, month, year, hour, seconds, etc. The DATEPART() and DATENAME() are similar except for the return type. DATEPART returns int and DATENAME returns nvarchar.

How to transact SQL yesterday date? ›

To get yesterday's date, you need to subtract one day from today's date. Use GETDATE() to get today's date (the type is datetime ) and cast it to date . In SQL Server, you can subtract or add any number of days using the DATEADD() function. The DATEADD() function takes three arguments: datepart , number , and date .

How do you use date diff? ›

To calculate the number of days between date1 and date2, you can use either Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1.

How to compare multiple dates in SQL? ›

Let us take a closer look at an example of how to compare dates in SQL Server using a basic SQL query: SELECT column_1, column_2, date_column, FROM table_name WHERE date_column >= 'YYYY-MM-DD'; In the syntax above, the output will contain three columns: column_1, column_2, and date_column from the table_name table.

Videos

1. SQL Query | Calculate number of weekdays between two dates | Exclude Weekends | DateDiff | DateName
(Learn at Knowstar)
2. SQL DATETIME FUNCTION DATEDIFF | How to calculate the difference between two date values in SQL
(The Data Millennials)
3. SQL DATEDIFF function - how to use SQL DATEDIFF function
(Super Collection Studio)
4. Lesson #11 How to use DateDiff function in TSQL
(CenteBoi)
5. SQL DATEDIFF Function Explained
(Ryan Nolan Data)
6. How to Subtract Dates in SQL Server (Years, Days, Hours, Seconds)
(Coding With Brett)
Top Articles
Latest Posts
Article information

Author: Errol Quitzon

Last Updated: 30/09/2023

Views: 5940

Rating: 4.9 / 5 (59 voted)

Reviews: 90% of readers found this page helpful

Author information

Name: Errol Quitzon

Birthday: 1993-04-02

Address: 70604 Haley Lane, Port Weldonside, TN 99233-0942

Phone: +9665282866296

Job: Product Retail Agent

Hobby: Computer programming, Horseback riding, Hooping, Dance, Ice skating, Backpacking, Rafting

Introduction: My name is Errol Quitzon, I am a fair, cute, fancy, clean, attractive, sparkling, kind person who loves writing and wants to share my knowledge and understanding with you.