How to remove Date from output?

Question

I have a query which gives the output as shown below.

SELECT *,
 DATEADD(SECOND, ( DATEDIFF(SECOND, InTime, OutTime) ), 0) AS Total
FROM TableXYZ

---

EmpName InTime OutTime DayStart NameOfMonth YearSet Total
ABC 12:11:32 21:53:24 Day1 November 2016 1/1/00 9:41
DEF 12:05:56 21:20:58 Day1 November 2016 1/1/00 9:15

I tried to remove date from Total but doesn't work. how can I do that and get the output below?

EmpName InTime OutTime DayStart NameOfMonth YearSet Total
ABC 12:11:32 21:53:24 Day1 November 2016 9:41
DEF 12:05:56 21:20:58 Day1 November 2016 9:15

• Martin Smith: Is it ever possible that OutTime - InTime can exceed 24 hours?

Answer 1

As you are working with SQL Server 2014 you could cast/convert as a time (started in SQL 2008)

SELECT *,
cast(DATEADD(SECOND, ( DATEDIFF(SECOND, InTime, OutTime) ), 0) as time(0)) AS Total
FROM TableXYZ

Answer 2

The result of your date calculation is being treated as a date by SQL. Try converting to a varchar value, then isolate the part you want returned. There might be cleaner solutions, but this one does the trick:

SELECT right(convert(varchar(19),DATEADD(SECOND, ( DATEDIFF(SECOND, getdate(), getdate()) ), 0),120),8)

This query converts the date to YYYY-MM-DD HH:MM:SS format, then gets the rightmost 8 characters.