SQL convert int in time

Question

I have a database that displays time as an integer. However, I want to display this in a report with the correct format. This is the format I would like to change:

eg.

183000 becomes 18:30 500 becomes 00:05 160000 becomes 16:00

etc.

I looked at both CAST and CONVERT, but did not manage to get this time in the correct format.

Answer 1

Assuming your input will always be 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

, datetime, , . .

int , / ( : 260000, 127900 ..).

Answer 2

, 100, , 10000, , 1000000, , , ,

:

Answer 3

varchar 4 , (500 0500), Left (myfield, 2) + ':' + right (myfield, 2)

, , . , varchar . , ( ? , , .

Answer 4

, SQL Server CONVERT .

DATEADD .

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