How can I convert integer to time?

Question

Select EmployeeName,Length
From Callers

Results:

John Doe  94559
John Doe  100720

This is basically in military time, so I believe that this data translates to 9:45 AM EST and 10:07 AM EST. When I add (Select dbo.convertIntToTime(Length)*100) I'm able to get it into regular time format. When I try (Select EmployeeName,AVG(Length) I get the average but in integers and I'm not sure how to convert it to time format.

Select EmployeeName,AVG(Length)
From Callers
Group By EmployeeName

Results:

John Doe 106546
Tom Cruise 116275
Lebron James 156971

Any help would be appreciated!

Answer 1

Those are not military time numbers. There is no 1569 in military or anytime for that matter. Military is 0000-2300 where 0000 would be 12:00AM and 2300 would be 11:00PM.

So 156971, if it was military time would be 3:69 PM and 71 seconds. Not possible.

Are you sure the time is being stored that way you think?

I would suggest changing the way it’s stored to a time stamp, would make it much easier to use.

Answer 2

Converting Integer to time SQL Server

Create Below function in Sql Server -

create function change_int_to_time
(
    @input int
)
returns time(2)
as
begin
       return dateadd(hour, (@input / 1000000) % 100,
              dateadd(minute, (@input / 10000) % 100,
              dateadd(second, (@input / 100) % 100, cast('00:00:00' as time(2)))))
end

Now Use this function wherever you want like in you case you can use it like below -

Example 1 :

Select EmployeeName,dbo.change_int_to_time(AVG(Length))
From Callers
Group By EmployeeName

Example 2 :

Select EmployeeName,dbo.change_int_to_time(Length)
From Callers
Group By EmployeeName

Answer 3

You can use TimeFromParts:

Select 
    EmployeeName, 
    Length,
    TimeFromParts(Length/ 10000, Length / 100 % 100, Length % 100, 0, 0) As TrueTime
From 
    Callers

To average, convert first to DateTime, then Float, average this, and convert back to time:

Select 
    EmployeeName, 
    Convert(Time(0), Convert(DateTime, Avg(Convert(Float, Convert(DateTime, TimeFromParts(Length / 10000, Length / 100 % 100, Length % 100, 0, 0)))))) As AverageLength
From 
    Callers
Group By
    EmployeeName