Convert smallint to time

问题:

I am trying to convert a smallint 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?

回答1:

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