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 successfully managed to get this the time in the correct format.

Answer 1

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.)