casting odd smallint time to to datetime format.

Question

Hello everyone, I'm working with a db (SQL server 2008), and have an interesting issue with times stored in the db.

The DBA who originally set it up was crafty and stored scheduled times as smallints in 12-hour form-- 6:00AM would be represented as 600. I've figured out how to split them into hours and minutes like thus:

select floor(time/100) as hr, right(time, 2) as min from table;

What I want to do is compare these scheduled times to actual times, which are stored in the proper datetime format. Ideally, I would do this with two datetime fields and use datediff() between them, but this would require converting the smallint time into a datetime, which I can't figure out.

Does anyone have suggestions on how to do this?

Thanks in advance.

Answer 1

Can think of two ways to do that. The first is to build a string in the HH:MM format, and cast that to datetime. The second is to convert the smallint format to float with the number of days as unit. The number of days is the internal time representation, so you can cast that to datetime too.

Example code:

declare @i smallint
set @i = 621

-- Cast to a string '6:21', then to a datetime
select cast(CAST(@i / 100 as varchar) + ':' + CAST(@i % 100 as varchar) 
    as datetime)

-- Convert to number of days, which is the interal datetime format
select cast((@i/100)/24.0 + (@i%100)/(24*60.0) as datetime)

P.S. If you divide an integer by another integer. the result is a third integer: 100 / 24 = 4. If you divide an integer by a float, the result is a float: 100 / 24.0 = 4.16666.

Answer 2

Since you are using SQL Server 2008, you can take advantage of the new Time data type. In order to convert the integer to a time value, we need to assume that the last two digits are minutes. To get the minute portion, divide by 100, take the integer portion and subtract it from the initial value. So in the case of 621 we get:

621 - Floor(621/100)* 100
621 - Floor(6.21)*100
621 - 6*100
621 - 600 = 21 minutes

For the hour portion, we can simply take the integer value after dividing by 100.

Create Table #Test( IntVal smallint not null )
Insert #Test Values( 621 )
Insert #Test Values( 2359 )
Insert #Test Values( 1200 )
Insert #Test Values( 1201 )
Insert #Test Values( 1159 )

Select Z.TimeVal, GetDate(), DateDiff(hh, Z.TimeVal, Cast(GetDate() As Time(0)))
From    (
        Select Cast(DateAdd(mi
                    , IntVal - Floor(IntVal/100)*100
                    , DateAdd(hh, Floor(IntVal/100), 0)
                    ) As Time(0)) As TimeVal
        From #Test
        ) As Z

Part of the trick here is to use DateAdd(hh, Floor(IntVal/100), 0) which does a DateAdd against the zero value for datetime.