Donald Bury Donald Bury - 4 months ago 26
SQL Question

Convert SmallInt to time

I am trying to convert a smallnt to a time format.

I am using SQL Server 2005 which doesn't support the time datatype.

I am storing the time as 1400 which is a smallint. When retrieving i want it to be converted to a time format. Such as 14:00

Any Ideas or guidance on the matter. If there is an easier way to do it or if the way i am trying is possible?

Thanks

Answer

You can get a result as varchar by using this:

SELECT 
    RIGHT('0' + CONVERT(varchar(10), yourTime / 100), 2) + ':' +  
    RIGHT('0' + CONVERT(varchar(10), yourTime % 100), 2) As timeString
FROM
    yourTable

You can also have a result in DATETIME format like this:

SELECT 
    CONVERT(datetime, CONVERT(varchar(10), yourTime / 100)+ ':' + CONVERT(varchar(10), yourTime % 100))
FROM
    yourTable

In SQL Server 2012+ you can have a result in time format:

SELECT 
    TIMEFROMPARTS(yourTime / 100, yourTime % 100, 0, 0, 0) As timeFormat
FROM 
    yourTable