Difference of two date time in sql server

Is there any way to take the difference between two datetime in sql server?

For example, my dates are

1. 2010-01-22 15:29:55.090
2. 2010-01-22 15:30:09.153

So, the result should be 开发者_C百科14.063 seconds.

---

Just a caveat to add about DateDiff, it counts the number of times you pass the boundary you specify as your units, so is subject to problems if you are looking for a precise timespan. e.g.

select datediff (m, '20100131', '20100201')

gives an answer of 1, because it crossed the boundary from January to February, so even though the span is 2 days, datediff would return a value of 1 - it crossed 1 date boundary.

select datediff(mi, '2010-01-22 15:29:55.090' , '2010-01-22 15:30:09.153')

Gives a value of 1, again, it passed the minute boundary once, so even though it is approx 14 seconds, it would be returned as a single minute when using Minutes as the units.

---

SELECT DATEDIFF (MyUnits, '2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')

Substitute "MyUnits" based on DATEDIFF on MSDN

---

SELECT  DATEDIFF(day, '2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')

Replace day with other units you want to get the difference in, like second, minute etc.

---

I can mention four important functions of MS SQL Server that can be very useful:

1) The function DATEDIFF() is responsible to calculate differences between two dates, the result could be "year quarter month dayofyear day week hour minute second millisecond microsecond nanosecond", specified on the first parameter (datepart):

select datediff(day,'1997-10-07','2011-09-11')

2) You can use the function GETDATE() to get the actual time and calculate differences of some date and actual date:

select datediff(day,'1997-10-07', getdate() )

3) Another important function is DATEADD(), used to convert some value in datetime using the same datepart of the datediff, that you can add (with positive values) or substract (with negative values) to one base date:

select DATEADD(day,  45, getdate()) -- actual datetime adding 45 days
select DATEADD(  s,-638, getdate()) -- actual datetime subtracting 10 minutes and 38 seconds

4) The function CONVERT() was made to format the date like you need, it is not parametric function, but you can use part of the result to format the result like you need:

select convert(  char(8), getdate() ,   8) -- part hh:mm:ss of actual datetime
select convert(  varchar, getdate() , 112) -- yyyymmdd
select convert( char(10), getdate() ,  20) -- yyyy-mm-dd limited by 10 characters

DATETIME cold be calculated in seconds and one interesting result mixing these four function is to show a formated difference um hours, minutes and seconds (hh:mm:ss) between two dates:

declare  @date1 datetime, @date2 datetime
set @date1=DATEADD(s,-638,getdate())
set @date2=GETDATE()

select convert(char(8),dateadd(s,datediff(s,@date1,@date2),'1900-1-1'),8)

... the result is 00:10:38 (638s = 600s + 38s = 10 minutes and 38 seconds)

Another example:

select distinct convert(char(8),dateadd(s,datediff(s, CRDATE , GETDATE() ),'1900-1-1'),8) from sysobjects order by 1

---

I tried this way and it worked. I used SQL Server version 2016

SELECT DATEDIFF(MILLISECOND,'2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')/1000.00;

Different DATEDIFF Functions are:

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

Ref: https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017

---

Ok we all know the answer involves DATEDIFF(). But that gives you only half the result you may be after. What if you want to get the results in human-readable format, in terms of Minutes and Seconds between two DATETIME values?

The CONVERT(), DATEADD() and of course DATEDIFF() functions are perfect for a more easily readable result that your clients can use, instead of a number.

i.e.

CONVERT(varchar(5), DATEADD(minute, DATEDIFF(MINUTE, date1, date2), 0), 114) 

This will give you something like:

HH:MM

If you want more precision, just increase the VARCHAR().

CONVERT(varchar(12), DATEADD(minute, DATEDIFF(MINUTE, date1, date2), 0), 114) 

HH:MM.SS.MS

---

There are a number of ways to look at a date difference, and more when comparing date/times. Here's what I use to get the difference between two dates formatted as "HH:MM:SS":

ElapsedTime AS
      RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate)        / 3600 AS VARCHAR(2)), 2) + ':'
    + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) % 3600 /   60 AS VARCHAR(2)), 2) + ':'
    + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) %   60        AS VARCHAR(2)), 2)

I used this for a calculated column, but you could trivially rewrite it as a UDF or query calculation. Note that this logic rounds down fractional seconds; 00:00.00 to 00:00.999 is considered zero seconds, and displayed as "00:00:00".

If you anticipate that periods may be more than a few days long, this code switches to D:HH:MM:SS format when needed:

ElapsedTime AS
    CASE WHEN DATEDIFF(S, StartDate, EndDate) >= 359999
        THEN
                          CAST(DATEDIFF(S, StartDate, EndDate) / 86400        AS VARCHAR(7)) + ':'
            + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) % 86400 / 3600 AS VARCHAR(2)), 2) + ':'
            + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) %  3600 /   60 AS VARCHAR(2)), 2) + ':'
            + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) %    60        AS VARCHAR(2)), 2)
        ELSE
              RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate)        / 3600 AS VARCHAR(2)), 2) + ':'
            + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) % 3600 /   60 AS VARCHAR(2)), 2) + ':'
            + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) %   60        AS VARCHAR(2)), 2)
        END

---

The following query should give the exact stuff you are looking out for.

select datediff(second, '2010-01-22 15:29:55.090' , '2010-01-22 15:30:09.153')

Here is the link from MSDN for what all you can do with datediff function . https://msdn.microsoft.com/en-us/library/ms189794.aspx

---

Internally in SQL Server dates are stored as 2 integers. The first integer is the number of days before or after the base date (1900/01/01). The second integer stores the number of clock ticks after midnight, each tick is 1/300 of a second.

More info here

Because of this, I often find the simplest way to compare dates is to simply substract them. This handles 90% of my use cases. E.g.,

select date1, date2, date2 - date1 as DifferenceInDays
from MyTable
...

When I need an answer in units other than days, I will use DateDiff.

---

SELECT DATEDIFF(yyyy, '2011/08/25', '2017/08/25') AS DateDiff

It's gives you difference between two dates in Year

Here (2017-2011)=6 as a result

Syntax:

DATEDIFF(interval, date1, date2)

---

Use This for DD:MM:SS:

SELECT CONVERT(VARCHAR(max), Datediff(dd, '2019-08-14 03:16:51.360', 
         '2019-08-15 05:45:37.610')) 
       + ':' 
       + CONVERT(CHAR(8), Dateadd(s, Datediff(s, '2019-08-14 03:16:51.360', 
         '2019-08-15 05:45:37.610'), '1900-1-1'), 8) 

---

So this isn't my answer but I just found this while searching around online for a question like this as well. This guy set up a procedure to calculate hours, minutes and seconds. The link and the code:

--Creating Function
If OBJECT_ID('UFN_HourMinuteSecond') Is Not Null
Drop Function dbo.UFN_HourMinuteSecond
Go
Exec(
'Create Function dbo.UFN_HourMinuteSecond
(
@StartDateTime DateTime,
@EndDateTime DateTime
) Returns Varchar(10) 
As
Begin

Declare @Seconds Int,
@Minute Int,
@Hour Int,
@Elapsed Varchar(10)

Select @Seconds = ABS(DateDiff(SECOND ,@StartDateTime,@EndDateTime))

If @Seconds >= 60 
Begin
select @Minute = @Seconds/60
select @Seconds = @Seconds%60

If @Minute >= 60
begin
select @hour = @Minute/60
select @Minute = @Minute%60
end

Else
Goto Final 
End

Final:
Select @Hour = Isnull(@Hour,0), @Minute = IsNull(@Minute,0), @Seconds =               IsNull(@Seconds,0)
select @Elapsed = Cast(@Hour as Varchar) + '':'' + Cast(@Minute as Varchar) + '':'' +     Cast(@Seconds as Varchar)

Return (@Elapsed)
End'
)

---

declare @dt1 datetime='2012/06/13 08:11:12', @dt2 datetime='2012/06/12 02:11:12'

select CAST((@dt2-@dt1) as time(0))

---

PRINT DATEDIFF(second,'2010-01-22 15:29:55.090','2010-01-22 15:30:09.153')

---

select
datediff(millisecond,'2010-01-22 15:29:55.090','2010-01-22 15:30:09.153') / 1000.0 as Secs

result:
Secs
14.063

Just thought I'd mention it.

---

Sol-1:

select 
  StartTime
  , EndTime
  , CONVERT(NVARCHAR,(EndTime-StartTime), 108) as TimeDiff 
from 
  [YourTable]

Sol-2:

select 
  StartTime
  , EndTime
  , DATEDIFF(hh, StartTime, EndTime)
  , DATEDIFF(mi, StartTime, EndTime) % 60 
from 
  [YourTable]

Sol-3:

select 
  DATEPART(hour,[EndTime]-[StartTime])
  , DATEPART(minute,[EndTime]-[StartTime]) 
from 
  [YourTable]

Datepart works the best

---

Please check below trick to find the date difference between two dates

 DATEDIFF(DAY,ordr.DocDate,RDR1.U_ProgDate) datedifff

where you can change according your requirement as you want difference of days or month or year or time.

---

CREATE FUNCTION getDateDiffHours(@fdate AS datetime,@tdate as datetime)
RETURNS varchar (50)
AS
BEGIN
    DECLARE @cnt int
    DECLARE @cntDate datetime
    DECLARE @dayDiff int
    DECLARE @dayDiffWk int
    DECLARE @hrsDiff decimal(18)
    
    DECLARE @markerFDate datetime
    DECLARE @markerTDate datetime
    
    DECLARE @fTime int
    DECLARE @tTime int 
    
    
    DECLARE @nfTime varchar(8)
    DECLARE @ntTime varchar(8)
    
    DECLARE @nfdate datetime
    DECLARE @ntdate datetime
    
    -------------------------------------
    --DECLARE @fdate datetime
    --DECLARE @tdate datetime

    --SET @fdate = '2005-04-18 00:00:00.000'
    --SET @tdate = '2005-08-26 15:06:07.030'
    -------------------------------------
    
    DECLARE @tempdate datetime
    
    --setting weekends
    SET @fdate = dbo.getVDate(@fdate)
    SET @tdate = dbo.getVDate(@tdate)
    --RETURN @fdate 

    SET @fTime = datepart(hh,@fdate)
    SET @tTime = datepart(hh,@tdate)
    --RETURN @fTime 
    if datediff(hour,@fdate, @tdate) <= 9
    
            RETURN(convert(varchar(50),0) + ' Days ' + convert(varchar(50),datediff(hour,@fdate, @tdate)))  + ' Hours'
    else
    --setting working hours
    SET @nfTime = dbo.getV00(convert(varchar(2),datepart(hh,@fdate))) + ':' +dbo.getV00(convert(varchar(2),datepart(mi,@fdate))) + ':'+  dbo.getV00(convert(varchar(2),datepart(ss,@fdate)))
    SET @ntTime = dbo.getV00(convert(varchar(2),datepart(hh,@tdate))) + ':' +dbo.getV00(convert(varchar(2),datepart(mi,@tdate))) + ':'+  dbo.getV00(convert(varchar(2),datepart(ss,@tdate)))
    
    IF @fTime > 17 
    begin
        set @nfTime = '17:00:00'
    end 
    else
    begin
        IF @fTime < 8 
            set @nfTime = '08:00:00'
    end 
    
    IF @tTime > 17 
    begin
        set @ntTime = '17:00:00'
    end 
    else
    begin
        IF @tTime < 8 
            set @ntTime = '08:00:00'
    end 

    
    
    -- used for working out whole days

    SET @nfdate = dateadd(day,1,@fdate) 

    SET @ntdate = @tdate
    SET @nfdate = convert(varchar,datepart(yyyy,@nfdate)) + '-' + convert(varchar,datepart(mm,@nfdate)) + '-' + convert(varchar,datepart(dd,@nfdate))
    SET @ntdate = convert(varchar,datepart(yyyy,@ntdate)) + '-' + convert(varchar,datepart(mm,@ntdate)) + '-' + convert(varchar,datepart(dd,@ntdate))
    SET @cnt = 0
    SET @dayDiff = 0 
    SET @cntDate = @nfdate
    SET @dayDiffWk = convert(decimal(18,2),@ntdate-@nfdate)
    
    --select @nfdate,@ntdate

    WHILE @cnt < @dayDiffWk
    BEGIN   
        IF (NOT DATENAME(dw, @cntDate) = 'Saturday') AND (NOT DATENAME(dw, @cntDate) = 'Sunday')
        BEGIN 
            SET @dayDiff = @dayDiff + 1
        END 
        SET @cntDate = dateadd(day,1,@cntDate)
        SET @cnt = @cnt + 1
    END 
    
    --SET @dayDiff = convert(decimal(18,2),@ntdate-@nfdate) --datediff(day,@nfdate,@ntdate)
    --SELECT @dayDiff
    
    set @fdate = convert(varchar,datepart(yyyy,@fdate)) + '-' + convert(varchar,datepart(mm,@fdate)) + '-' + convert(varchar,datepart(dd,@fdate)) + ' ' + @nfTime
    set @tdate = convert(varchar,datepart(yyyy,@tdate)) + '-' + convert(varchar,datepart(mm,@tdate)) + '-' + convert(varchar,datepart(dd,@tdate)) + ' ' + @ntTime
    
    set @markerFDate = convert(varchar,datepart(yyyy,@fdate)) + '-' + convert(varchar,datepart(mm,@fdate)) + '-' + convert(varchar,datepart(dd,@fdate)) + ' ' + '17:00:00'
    set @markerTDate = convert(varchar,datepart(yyyy,@tdate)) + '-' + convert(varchar,datepart(mm,@tdate)) + '-' + convert(varchar,datepart(dd,@tdate)) + ' ' + '08:00:00'
    
    --select @fdate,@tdate
    --select @markerFDate,@markerTDate
    
    set @hrsDiff = convert(decimal(18,2),datediff(hh,@fdate,@markerFDate))
    
    --select @hrsDiff
    set @hrsDiff = @hrsDiff +  convert(int,datediff(hh,@markerTDate,@tdate))

    --select @fdate,@tdate  
    
    IF convert(varchar,datepart(yyyy,@fdate)) + '-' + convert(varchar,datepart(mm,@fdate)) + '-' + convert(varchar,datepart(dd,@fdate)) = convert(varchar,datepart(yyyy,@tdate)) + '-' + convert(varchar,datepart(mm,@tdate)) + '-' + convert(varchar,datepart(dd,@tdate))  
    BEGIN
        --SET @hrsDiff = @hrsDiff - 9
        Set @hrsdiff = datediff(hour,@fdate,@tdate)
    END 
    
    --select FLOOR((@hrsDiff / 9))
    
    IF (@hrsDiff / 9) > 0 
    BEGIN
        SET @dayDiff = @dayDiff + FLOOR(@hrsDiff / 9)
        SET @hrsDiff = @hrsDiff - FLOOR(@hrsDiff / 9)*9
    END 

    --select convert(varchar(50),@dayDiff) + ' Days ' + convert(varchar(50),@hrsDiff)   + ' Hours'

    RETURN(convert(varchar(50),@dayDiff) + ' Days ' + convert(varchar(50),@hrsDiff))    + ' Hours'

END

---

For Me This worked Perfectly Convert(varchar(8),DATEADD(SECOND,DATEDIFF(SECOND,LogInTime,LogOutTime),0),114)

and the Output is HH:MM:SS which is shown accurately in my case.

---

Please try

DECLARE @articleDT DATETIME;
DECLARE @nowDate DATETIME;
 
-- Time of the ARTICLE created
SET @articleDT = '2012-04-01 08:10:16';
 
-- Simulation of NOW datetime
-- (in real world you would probably use GETDATE())
SET @nowDate = '2012-04-10 11:35:36';
 
-- Created 9 days ago.
SELECT 'Created ' + CAST(DATEDIFF(day, @articleDT, @nowDate) AS NVARCHAR(50)) + ' days ago.';
 
-- Created 1 weeks, 2 days, 3 hours, 25 minutes and 20 seconds ago.
SELECT 'Created '
    + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 / 24 / 7 AS NVARCHAR(50)) + ' weeks, '
    + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 / 24 % 7 AS NVARCHAR(50)) + ' days, '
    + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 % 24  AS NVARCHAR(50)) + ' hours, '
    + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 % 60 AS NVARCHAR(50)) + ' minutes and '
    + CAST(DATEDIFF(second, @articleDT, @nowDate) % 60 AS NVARCHAR(50)) + ' seconds ago.';