SQL convert int to time

Question

I have a database that displays time as an integer. However I am wanting to output this into a report with the correct format. This is the format that I would like to change:

eg.

183000 would become 18:30 500 would become 00:05 160000 would become 16:00

and so on.

I have had a look and CAST and CONVERT but not succefully managed to get this the time in the correct format.

Answer 1

looks like you need to divide by 100 to get the seconds, divide by 10000 to get the minutes, and divide by 1000000 to get the hours, then format those values as a string, inserting a colon between hours and minutes, like

hh:mm

Answer 2

First cast to a varchar field and convert all times to 4 digits adding leading zeros if need be (500 would become 0500)and then break up the field with concantenation Left(myfield,2) + ':' + right(myfield,2)

This is something stupid to do every time you run a report, it is wasteful of server resources. If possible change the field to varchar and runthe code once. If not possible, can you add a formatted field and have a trigger do the formatiing on insertion (you'll still need to update the field the first time? Possibly a constraint would do instead of a trigger, but that would depend on the database.

Answer 3

Assuming your input will always be an int, you can parse it with something like:

DECLARE @stringTime varchar(6)

SET @stringTime =  RIGHT('000000' + CAST(intTime AS VARCHAR), 6)

SELECT CAST(LEFT(@stringTime, 2) + ':' + RIGHT(LEFT(@stringTime, 4), 2) AS TIME) as TimeValue

I'd DEFINITELY look to change this field to an actual time or datetime field, as this level of conversion is not advised, especially for a heavily used database. There's just really got to be a better way to store your data.

Using an int value this way allows for a lot of bad data, without adding a lot of additional checks and/or constraints on your inputs (i.e.: 260000, 127900, etc.)

Answer 4

I'm assuming that you are on SQL Server based on use of CONVERT and your previous questions.

You could use DATEADD for this too.

WITH Times AS
(
SELECT 183000 AS T 
UNION ALL 
SELECT 500 
UNION ALL  
SELECT 160000 

)
SELECT CAST(DATEADD(SECOND, T%100 + (60*(T%10000 / 100)) + 3600*(T/10000),0)
            AS time /*Or datetime if < SQL Server 2008*/)
FROM Times