Link to home
Start Free TrialLog in
Avatar of Chris Jones
Chris JonesFlag for United States of America

asked on

how to just get time from a date

hello

I am just trying to get a time from the query I have and its giving me the default date then my time. The time is the only part that I need in that field.

What i get
1900-01-01 00:00:55.000

Open in new window


what i need
00:00:55

Open in new window


my SQL
 select  datediff(hour, 0, cast(CallTime as time)),
  DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000',  convert(varchar(10), dateadd(second, Duration, 0), 108))), '00:00:00.000') as time
  from NT_API_CallLogs
 WHERE Duration <> 0
 AND cast(CallTime as date) = '2017-3-08'
GROUP BY datediff(hour, 0, cast(CallTime as time) )
ORDER BY datediff(hour, 0, cast(CallTime as time) )

Open in new window

Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

cast the result of the call to datediff as a Time value, not the parameter that you're passing.
Avatar of Chris Jones

ASKER

but this line is the line I need in just hours minutes sand seconds call time returns the correct result i need

this is the line i need to remove the default date from.
  DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000',  convert(varchar(10), dateadd(second, Duration, 0), 108))), '00:00:00.000') as time

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
AWESOME Scott - am i able to remove the trailing 0's i was thinking to substring it maybe.
If you just want HH:MM, you can do this:

CONVERT(varchar(5), CAST(DATEADD(SECOND, SUM(Duration), 0) AS time), 8) AS time

If you want HH:MM:SS, change to "varchar(8)".
Thanks this worked great