How can I convert integer to time?-CodePudding

I'm trying to find the average time an employee is making their first call.

Select EmployeeName,FirstCall
From Calls

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(FirstCall)*100) I'm able to get it into regular time format. When I try (Select EmployeeName,AVG(FirstCall) I get the average but in integers and I'm not sure how to convert it to time format.

I think part of the issue is that the data return has minutes values over 60.

Select EmployeeName,AVG(FirstCall)
From Calls
Group By EmployeeName

Results:

John Doe 106546
Tom Cruise 116275
Lebron James 156971

Any help would be appreciated!

CodePudding user response：

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.

CodePudding user response：

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(FirstCall))
From Calls
Group By EmployeeName

Example 2 :

Select EmployeeName,dbo.change_int_to_time(FirstCall)
From Calls
Group By EmployeeName

CodePudding user response：

You can use TimeFromParts:

Select 
    EmployeeName, 
    FirstCall,
    TimeFromParts(FirstCall / 10000, FirstCall / 100 % 100, FirstCall % 100, 0, 0) As TrueTime
From 
    Calls

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(FirstCall / 10000, FirstCall / 100 % 100, FirstCall % 100, 0, 0)))))) As AverageFirstCall
From 
    Calls
Group By
    EmployeeName