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

There are SQL functions that do things similar to what you're trying:

DatePart will get a specific part of a date. So you could use this with your Duration field to format it however you like.

DateDiff will get the difference between two dates. you may be able to replace the entire subtraction with this.

Answer 2

Try converting to varchar() before applying RIGHT()

Answer 3

You probably need the following T-SQL:

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

Answer 4

Off the top of my head the easiest way to do this is to do your calculation twice and then concatenate the two strings. This can be performed in a single select statement.

-- example
select cast(day(getdate()) as varchar) + ' ' + convert(varchar, getdate(), 108)

-- your code
SELECT cast(day(EndDtField - StartDtField) as varchar) + ' ' +
       convert(varchar, EndDtField - StartDtField, 108) AS Duration
  FROM myTable;

Answer 5

try this

SELECT CAST(DATEPART(DAY,(EndDtField  - StartDtField)) AS VARCHAR)+' '+CAST(DATEPART(HOUR,(EndDtField  - StartDtField)) AS VARCHAR)+':'+CAST(DATEPART(MINUTE,(EndDtField  - StartDtField)) AS VARCHAR)+':'+CAST(DATEPART(SECOND,(EndDtField  - StartDtField)) AS VARCHAR)
  FROM myTable;

Bye

Answer 6

Convert(DateTime,Convert(char,GetDate(),101),101)