кастинг нечетного smallint времени к к формату даты и времени
Я работаю с дб (SQL-сервер 2008) и имею интересную проблему с временами, сохраненными в дб.
DBA, кто первоначально настроил его, был лукавым и сохранил запланированные времена как smallints в 12-часовой форме - 6:00 будет представлено как 600. Я выяснил, как разделить их на часы и минуты как таким образом:
select floor(time/100) as hr, right(time, 2) as min from table;
То, что я хочу сделать, сравнивают эти запланированные времена с фактическими временами, которые хранятся в надлежащем формате даты и времени. Идеально, я сделал бы это с двумя полями даты и времени и использовал бы datediff () между ними, но это потребует преобразования smallint времени в дату и время, которую я не могу выяснить.
У кого-либо есть предложения о том, как сделать это?
Заранее спасибо.
2 ответа
Можно придумать два способа сделай это. Первый - создать строку в формате HH: MM и преобразовать ее в datetime . Второй - преобразовать формат smallint в float с количеством дней в качестве единицы. Количество дней - это внутреннее представление времени, поэтому вы также можете преобразовать его в datetime .
Пример кода:
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. Если вы разделите целое число на другое целое число. результат - третье целое число: 100/24 = 4 . Если вы разделите целое число на число с плавающей запятой, результатом будет число с плавающей запятой: 100 / 24,0 = 4,16666 .
Поскольку вы используете SQL Server 2008, вы можете воспользоваться преимуществами нового типа данных Time. Чтобы преобразовать целое число в значение времени, нужно предположить, что последние две цифры - это минуты. Чтобы получить минутную часть, разделите на 100, возьмите целую часть и вычтите ее из исходного значения. Таким образом, в случае 621 мы получаем:
621 - Floor(621/100)* 100
621 - Floor(6.21)*100
621 - 6*100
621 - 600 = 21 minutes
Для часовой части мы можем просто взять целое значение после деления на 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
Часть хитрости здесь заключается в использовании DateAdd(hh, Floor(IntVal/100), 0), который делает DateAdd против нулевого значения для datetime.