Get time in HH:MM:SS.nnn format?

Question

How can I get the time in the format of HH:MM:SS.nnn using the SQL convert() function

I currently using this to get the time difference in HH:MM:SS format

CONVERT(NVARCHAR(12), DATEADD(MILLISECOND, ISNULL(DATEDIFF(MILLISECOND, T.IssueDatetime, T.CalledDateTime), 0), 0),108)

But now I want the time format to show milliseconds in the form of HH:MM:SS.nnn with the (.) as a separator not the (:) as how the format code 114 can do.

How do I achieve this?

BTW - that format would normally be expressed as `HH:mm:ss.mmm` if you want 24 hour time and `hh:mm:ss.mmm` if not — Dale K, May 31 '23 at 07:27

Paul Maxwell · Answer 1 · 2023-05-31T09:32:49.260

Using datediff in milliseconds can overflow easily, so be cautious of relying on that. (Max. duration is is 24 days, 20 hours, 31 minutes and 23.647 seconds.)

Style 108 produces HH:MM:SS , whereas style 114 supplies HH:MM:SS.nnn , or you can use FORMAT(..., 'hh:mm:ss.fff') e.g:

CONVERT(NVARCHAR(12), 
   DATEADD(MILLISECOND, ISNULL(DATEDIFF(MILLISECOND, T.IssueDatetime, T.CalledDateTime), 0), 0)
     ,114)

However DATEDIFF(MILLISECOND, T.IssueDatetime, T.CalledDateTime) could be greater than 24 hours, if that is the case no "time only" style is going to display hours larger than 23. So if you need to cater for more than 24 hours you will need to calculate your own hh:mm:ss.nnn string - example below

CREATE TABLE t (
  IssueDatetime DATETIME,
  CalledDateTime DATETIME
);


INSERT INTO t (IssueDatetime, CalledDateTime)
VALUES ('2023-05-30 10:30:00.002', '2023-06-01 14:53:42.346');

1 rows affected

select
     by_style114 = CONVERT(NVARCHAR(12), 
          DATEADD(MILLISECOND, ISNULL(DATEDIFF(MILLISECOND, T.IssueDatetime, T.CalledDateTime), 0), 0) 
         ,114) 
  , by_format = FORMAT(DATEADD(MILLISECOND, ISNULL(DATEDIFF(MILLISECOND, T.IssueDatetime, T.CalledDateTime), 0), 0), 'hh:mm:ss.fff')
  , by_concat = concat(DATEDIFF(hour, T.IssueDatetime, T.CalledDateTime) 
               , ':' , DATEDIFF(minute, T.IssueDatetime, T.CalledDateTime) % 60 
               , ':' , DATEDIFF(second, T.IssueDatetime, T.CalledDateTime) % 60 
               , '.' , DATEDIFF(MILLISECOND, T.IssueDatetime, T.CalledDateTime) % 188622000 )
from t

by_style114	by_format	by_concat
04:23:42:343	04:23:42.343	52:23:42.343

fiddle

Using `VARCHAR` without a length is a [bad habit](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length), even if it happens to work in this case because it's defaulting to `VARCHAR(30)`. Repeated conversion and concatenation is more succinctly done with `CONCAT`, as it takes care of the conversion on its own. — Jeroen Mostert, May 31 '23 at 09:21
I keep forgetting that concat() has more than 2 parameters in TSQL now, so yes - that would be much more succinct. — Paul Maxwell, May 31 '23 at 09:30

Get time in HH:MM:SS.nnn format?

1 Answers1