CODEWITHSUNDEEP
sqlsql-server
007
007

Reputation: 2186

DateTime Difference - SQL

I am trying to get the DateTime difference between two DateTime values.

Query I have so far,

DECLARE @start datetime = '2012-01-01 12:00:00.000'
DECLARE @end datetime =  '2013-01-01 11:59:59.999'

SELECT 
      CONVERT(VARCHAR(5),DATEDIFF(YYYY, @start, @end))
+'-'+ CONVERT(VARCHAR(5),DATEDIFF(MONTH, @start, @end))
+'-'+ CONVERT(VARCHAR(5),DATEDIFF(SECOND, @start, @end)/86400)
+' '+ CONVERT(VARCHAR(5),DATEDIFF(SECOND, @start, @end)%86400/3600)
+':'+ CONVERT(VARCHAR(5),DATEDIFF(SECOND, @start, @end)%3600/60)
+':'+ CONVERT(VARCHAR(5),(DATEDIFF(SECOND, @start, @end)%60)) 
+'.'+ CONVERT(VARCHAR(5),(DATEDIFF(SECOND, @start, @end)%100))
AS [YYYY-MM-DD HH:MM:SS.MSS]

Desired Output

0001-00-00 23:59:59.999
365 Days, 23 Hours, 59 Minutes, 59 Seconds, 999 Milliseconds

Actual Output 

1-12-366 8784:0:0.0

Thank you

Upvotes: 1

Views: 373

Answers (2)

Kaf
Kaf

Reputation: 33809

Datetime accuracy is rounded to increments of .000, .003, or .007 seconds. For better accuracy use DateTime2 datatype.

DECLARE @start datetime2 = '2012-01-01 12:00:00.000'
DECLARE @end datetime2 =  '2013-01-01 11:59:59.999'

--I do this way because millisecond difference overflows the integer.
select datediff(day, @start, @end) days,
       datediff(millisecond, convert(time, @start), convert(time, @end)) milliseconds

Results and fiddle demo:

   days | milliseconds
   366  | -1

Above results means you can get desired results:

  (366 days - 1 millisecond) =  
   365 Days, 23 Hours, 59 Minutes, 59 Seconds, 999 Milliseconds

EDIT: Please note that millisecond difference between given two dates are too high and in fact overflowing bigint as well. So I use THIS APPROACH to get your expected answer.

Upvotes: 5

Decker97
Decker97

Reputation: 1653

I had to change the Start and End, it seems like SQL Server was rounding it when it was .999.

DECLARE @start datetime = '2012-01-01 00:00:00.000'
DECLARE @end datetime =  '2013-01-01 23:59:59.997'

SELECT 
      CONVERT(VARCHAR(5),DATEDIFF(YYYY, @start, @end))
+'-'+ CONVERT(VARCHAR(5),DATEPART(mm, @Start) - DATEPART(mm, @End))
+'-'+ CONVERT(VARCHAR(5),DATEPART(dd, @Start) - DATEPART(mm, @End))
+' '+ CONVERT(VARCHAR(5),DATEPART(hh, @Start) - DATEPART(hh, @End))
+':'+ CONVERT(VARCHAR(5),DATEPART(mi, @Start) - DATEPART(mi, @End))
+':'+ CONVERT(VARCHAR(5),DATEPART(ss, @Start) - DATEPART(ss, @End))
+'.'+ CONVERT(VARCHAR(5),DATEPART(ms, @Start) - DATEPART(ms, @End))
AS [YYYY-MM-DD HH:MM:SS.MSS]

Upvotes: 1

Related Questions