Difference of Two Date Time in SQL Server

Get Time Difference between two datetime in stored procedure

SELECT DATEDIFF(year, @startdate, @enddate)

try this to start you in the right direction.

year represents the period of measure you want to return

Here is a link to an MSDN Article that may be helpful

DATEDIFF (Transact-SQL)

Other Versions

Updated: December 2, 2015

THIS TOPIC APPLIES TO:

yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse
Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.
For larger differences, see DATEDIFF_BIG (Transact-SQL). For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).
Topic link icon Transact-SQL Syntax Conventions
Syntax

DATEDIFF ( datepart , startdate , enddate )

-- Azure SQL Data Warehouse and Parallel Data Warehouse

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 Configure the two digit year cutoff Server Configuration 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.
datepart Boundaries
The following statements have the same startdate and the same endate. Those dates are adjacent and differ in time by .0000001 second. The difference between the startdate and endate in each statement crosses one calendar or time boundary of its datepart. Each statement returns 1. If different years are used for this example and if both startdate and endate are in the same calendar week, the return value for week would be 0.

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');

Remarks
DATEDIFF can be used in the select list, WHERE, HAVING, GROUP BY and ORDER BY clauses.
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 is deterministic.
Examples
The following examples use different types of expressions as arguments for the startdate and enddate parameters.
A. Specifying columns for startdate and enddate
The following example calculates the number of day boundaries that are 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
The following example uses user-defined variables 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
The following 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
The following 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
The following 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
The following example uses a numeric expression, (GETDATE ()+ 1), and scalar system functions, GETDATE and SYSDATETIME, as arguments for enddate.

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
The following 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
The following 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

Examples: Azure SQL Data Warehouse Public Preview and Parallel Data Warehouse
The following examples use different types of expressions as arguments for the startdate and enddate parameters.
I. Specifying columns for startdate and enddate
The following example calculates the number of day boundaries that are 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
J. Specifying scalar subqueries and scalar functions for startdate and enddate
The following 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;

K. Specifying constants for startdate and enddate
The following 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;

L. Specifying ranking functions for startdate
The following 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;

M. Specifying an aggregate window function for startdate
The following 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

SQL time difference between two dates result in hh:mm:ss

declare @StartDate datetime, @EndDate datetime

select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'

select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss]

This query will helpful to you.

Difference between two date& time in datetime Fields in SQL

If you're talking about SQL Server, DATEDIFF is what you need.

Something like:

SELECT DATEDIFF(d, StartDate, EndDate) FROM tableName

... just substitute StartDate and EndDate for your column names, and tableName for the actual table name.
Also, you can swap out the 'd' character for other dateparts, say if you wanted to find the date difference in months, years etc.

* Update *

@sateesh, try and follow this. I think the issue you may be having is to do with rounding in SQL's DATEDIFF function. The way around this is to go down to a more granular level, such as minutes instead of days. See the sample code below, and compare the outputs:

DECLARE @tblDummy TABLE(Value1 SMALLDATETIME, Value2 SMALLDATETIME, [Description] NVARCHAR(50))
INSERT INTO @tblDummy (Value1, Value2, [Description]) VALUES ('2012-01-01 12:00', '2012-01-02 01:00', '13 hours 0 mins')
INSERT INTO @tblDummy (Value1, Value2, [Description]) VALUES ('2012-01-01 12:00', '2012-01-02 11:59', '23 hours 59 mins')
INSERT INTO @tblDummy (Value1, Value2, [Description]) VALUES ('2012-01-01 12:00', '2012-01-02 13:00', '25 hours 0 mins')
INSERT INTO @tblDummy (Value1, Value2, [Description]) VALUES ('2012-01-01 12:00', '2012-01-03 12:00', '48 hours 0 mins')
INSERT INTO @tblDummy (Value1, Value2, [Description]) VALUES ('2012-01-01 12:00', '2012-01-03 12:01', '48 hours 1 min')
INSERT INTO @tblDummy (Value1, Value2, [Description]) VALUES ('2012-01-01 12:00', '2012-01-04 00:00', '60 hours 0 mins')

-- Attempt 1: Standard date diff
SELECT DATEDIFF(d, Value1, Value2) [diff], [Description]
FROM @tblDummy

-- Attempt 2: Date diff taking it down to the minutes level
SELECT CEILING((DATEDIFF(minute, Value1, Value2) / 60.0) / 24.0) [diff], [Description]
FROM @tblDummy

Here's the output:

I believe Attempt 2 gives you what you need. If that doesn't help you, then I'm afraid I just don't understand your question.

TSQL, time difference between two dates

I tried the following and got 540.

declare @Start datetime
declare @End datetime
set @Start = '2017-2-23 17:00:00'
set @End = '2017-2-24 2:00:00'

select datediff(minute, @Start, @End)

Perhaps it would be useful to also print out the Start and End dates?

SELECT datediff (minute, START, END), START, END from TIME;

Time Difference between two dates in Minutes and hours like 1hr 20Min in SQL Server 2012

Here is an example:

DECLARE @sd DATETIME = '2015-11-03 10:45:35.747'
DECLARE @ed DATETIME = '2015-11-03 15:20:35.747'

SELECT CAST(DATEDIFF(ss, @sd, @ed) / 3600 AS VARCHAR(10)) + ':' +
       CAST((DATEDIFF(ss, @sd, @ed) -  3600 * (DATEDIFF(ss, @sd, @ed) / 3600)) / 60 AS VARCHAR(10)) + ' Hr'

Output:

4:35 Hr

TSQL - DateTime difference between more than two rows

Given a building entry, finding the first leave after that entry can be done with cross apply:

select entry.EmployeeId, entry.DateTime, exit.DateTime
from Events entry
  cross apply (select top 1 e.DateTime
               from Events e
               where e.EmployeeId = entry.EmployeeId
                 and e.DateTime > entry.DateTime
                 and e.EventTypeId = 2
               order by e.DateTime asc
              ) as exit
where entry.EventTypeId = 1

at which point you just need to use the applicable T/SQL function to get the difference in whatever unit you want (eg. in minutes with datediff(minute, entry.DateTime, exit.DateTime).

To get the total of all the differences simply sum the differences:

select EmployeeId, sum(mins)
from (
    select entry.EmployeeId, entry.DateTime as EntryDateTime, exit.DateTime as ExitDateTime, datediff(minute, EntryDateTime, ExitDateTime) as mins
    from Events entry
      cross apply (select top 1 e.DateTime
                   from Events e
                   where e.EmployeeId = entry.EmployeeId
                     and e.DateTime > entry.DateTime
                     and e.EventTypeId = 2
                   order by e.DateTime asc
                  ) as exit
    where entry.EventTypeId = 1
) as input
group by EmployeeId

Edit: added overall summation (with diff on the inside for clarity)

---

Related Topics