TSQL Get partial string of DateTime value?

Question

I have the following TSQL query to get date time value different:

SELECT CONVERT(DATETIME, EndDtField - StartDtField, 120) AS Duration
  FROM myTable;

The result will be:

  Duration
  1900-01-01 23:02:04.000
  ....

I tried to get partial string from the result like this 01 23:02:04.000 by using RIGHT() function (actually preferably in the format of 01 23:02:04 without 000):

SELECT RIGHT(CONVERT(DATETIME, EndDtField - StartDtField, 120), 15) AS Duration
  FROM myTable;

The result is:

 Duration
 1 1900 11:02PM
 ...

It looks like that the value of RIGHT(..) is a datetime value. Even I specify the datetime value in a format of yyyy-mm-dd hh:mi:ss but still I cannot get my expected result. How can I get a partial string with only day and time(dd hh:mi:ss) out from the Duration? I really don't like to parse out day, hour, minute and second values and then to build a string.

Any other alternatives?

Answer 1

You probably need the following T-SQL:

SELECT RIGHT(CONVERT(VARCHAR, EndDtField - StartDtField, 121), 15) AS Duration FROM myTable;