Search code examples
sql-servert-sqltimeint

SQL convert int to time

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.

Solution

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