Avatar of Chris Jones
Chris Jones
Flag 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

Microsoft SQL ServerSQL.NET Programming

Avatar of undefined
Last Comment
Chris Jones

8/22/2022 - Mon
Kent Olsen

cast the result of the call to datediff as a Time value, not the parameter that you're passing.
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
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Chris Jones

ASKER
AWESOME Scott - am i able to remove the trailing 0's i was thinking to substring it maybe.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scott Pletcher

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)".
Chris Jones

ASKER
Thanks this worked great