CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
Pieter_Daems
Pieter_Daems

Reputation: 1264

Show datediff as seconds, milliseconds

I'm trying to calculate the difference between two datetime values.

I tried datediff(s, begin,end) and datediff(ms, begin,end) however I want the difference to be returned as seconds,milliseconds like the following:

4,14
63,54

Upvotes: 26

Views: 91684

Answers (2)

Wolfgang
Wolfgang

Reputation: 2328

Actually, the marked answer originally produced wrong results for milliseconds 1 - 99:

Example 1 second, 27 milliseconds:

  1. DATEDIFF % 1000 will return 27
  2. CONVERT will convert to '27'
  3. String concatenation will build '1' + ',' + '27'
  4. Result: '1.27' which means 270ms rather than 27ms

Don't forget to pad the milliseconds to three zeros:

DECLARE @start datetime2(7) = '2015-07-03 09:24:33.000'
DECLARE @end datetime2(7) = '2015-07-03 09:24:34.027'

SELECT 
    CAST (DATEDIFF(SECOND, @start, @end) AS nvarchar(3)) + N'.' +
    RIGHT('000' + CAST((DATEDIFF(MILLISECOND, @start, @end) % 1000) AS nvarchar(3)), 3)

Upvotes: 8

anon
anon

Reputation: 

SELECT 
  DATEDIFF(MILLISECOND, begin, end) / 1000, 
  DATEDIFF(MILLISECOND, begin, end) % 1000
FROM ...;

If you absolutely must form it as a string in your SQL query (can't your presentation tier do that?), then:

SELECT 
  CONVERT(VARCHAR(12),  DATEDIFF(MILLISECOND, begin, end) / 1000)
  + ',' 
  + RIGHT('000' + CONVERT(VARCHAR(4), DATEDIFF(MILLISECOND, begin, end) % 1000), 3)
FROM ...;

Also I really hope you have better column names than begin and end.

Upvotes: 46

Related Questions