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
what i need
my SQL
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
what i need
00:00:55
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) )
Microsoft SQL ServerSQL.NET Programming
Last Comment
Chris Jones
cast the result of the call to datediff as a Time value, not the parameter that you're passing.
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.
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
ASKER CERTIFIED SOLUTION
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.
ASKER
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)".
CONVERT(varchar(5), CAST(DATEADD(SECOND, SUM(Duration), 0) AS time), 8) AS time
If you want HH:MM:SS, change to "varchar(8)".
ASKER
Thanks this worked great