DATEDIFF (Transact-SQL)

Syntax

DATEDIFF ( datepart , startdate , enddate )

Arguments

• datepart
  Is the part of startdate and enddate that specifies the type of boundary crossed. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

  datepart	Abbreviations
  year	yy, yyyy
  quarter	qq, q
  month	mm, m
  dayofyear	dy, y
  day	dd, d
  week	wk, ww
  hour	hh
  minute	mi, n
  second	ss, s
  millisecond	ms
  microsecond	mcs
  nanosecond	ns

• startdate
  Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable or string literal. startdate is subtracted from enddate.

  To avoid ambiguity, use four-digit years. For information about two digits years, see two digit year cutoff Option.

• enddate
  See startdate.

Return Type

int

Return Value

• Each datepart and its abbreviations return the same value.

If the return value is out of range for int (-2,147,483,648 to +2,147,483,647), an error is returned. 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.

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

A time zone offset component of startdate or endate is not used in calculating the return value.

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

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

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

Remarks

DATEDIFF can be used in the select list, WHERE, HAVING, GROUP BY and ORDER BY clauses.

Starting with SQL Server 2008, DATEDIFF implicitly casts string literals as a datetime2 type. This means that DATEDIFF no longer supports 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.

Examples

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

A. Specifying columns for startdate and enddate

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

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

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

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

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

E. Specifying constants 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

USE AdventureWorks2008R2;
GO
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE()+ 1) 
    AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO
USE AdventureWorks2008R2;
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

USE AdventureWorks2008R2;
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

USE AdventureWorks2008R2;
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

See Also

Reference