Difference of two date time in sql server

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 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://docs.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

Internally in SQL Server dates are stored as 2 integers. The first integer is the number of dates 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) 

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

Check DateDiff out on Books Online.