We help IT Professionals succeed at work.

how to just get time from a date

117 Views
Last Modified: 2017-03-09
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

Comment
Watch Question

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
cast the result of the call to datediff as a Time value, not the parameter that you're passing.
Chris JonesLead Application Web Developer

Author

Commented:
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

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Chris JonesLead Application Web Developer

Author

Commented:
AWESOME Scott - am i able to remove the trailing 0's i was thinking to substring it maybe.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 JonesLead Application Web Developer

Author

Commented:
Thanks this worked great

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions