select cretor_date ,chg_date,cast((chg_date-cretor_date) as float) as tt from basetbl 我這樣試過后﹐得到的結果如下﹐請再幫幫忙看看﹗
Cretor_date chg_date tt
2002-08-21 07:53:54.000 2002-08-21 07:55:02.000 7.87037033703705E-4
2002-08-20 22:58:02.000 2002-08-20 22:58:16.000 1.62037037033703E-4
2002-08-20 22:39:44.000 2002-08-20 22:39:53.000 1.04166666666667E-4
2002-08-20 23:10:40.000 2002-08-23 21:19:11.000 2.92258101855184
2002-08-20 22:29:17.000 2002-08-20 22:29:22.000 5.78370370370373E-5
Cretor_date chg_date tt
2002-08-21 07:53:54.000 2002-08-21 07:55:02.000 7.87037033703705E-4
2002-08-20 22:58:02.000 2002-08-20 22:58:16.000 1.62037037033703E-4
2002-08-20 22:39:44.000 2002-08-20 22:39:53.000 1.04166666666667E-4
2002-08-20 23:10:40.000 2002-08-23 21:19:11.000 2.92258101855184
2002-08-20 22:29:17.000 2002-08-20 22:29:22.000 5.78370370370373E-5
select cretor_date ,chg_date,convert(char(10),(chg_date)-(cretor_date) ,108) as tt from basetbl
cretor_date chg_date tt
2002-08-21 07:53:54.000 2002-08-21 07:55:02.000 00:01:08
2002-08-20 22:58:02.000 2002-08-20 22:58:16.000 00:00:14
2002-08-20 22:39:44.000 2002-08-20 22:39:53.000 00:00:09
2002-08-20 23:10:40.000 2002-08-23 21:19:11.000 22:08:31
2002-08-20 22:29:17.000 2002-08-20 22:29:22.000 00:00:05
2002-08-20 22:47:43.000 2002-08-20 22:49:08.000 00:01:25
2002-08-20 22:37:42.000 2002-08-20 22:37:54.000 00:00:12
select cast(datediff(minute,'2002-08-21 07:53:54.000','2002-08-21 11:10:54.000')/60.0 as numeric(4,2))
select dbo.CalHalfHour(cast(datediff(minute,'2002-08-21 07:53:54.000','2002-08-21 11:10:54.000')/60.0 as numeric(4,2)))其中函数为自定义函数,如下:create function dbo.CalHalfHour
(@numInput numeric(4,2))
returns numeric(4,2)
as
begin
declare @numOutput numeric(4,2)
declare @numTmp numeric(4,2)
set @numTmp=@numInput-floor(@numInput)
select @numOutput = case
when @numTmp<0.25 then floor(@numInput)
when @numTmp>=0.25 and @numTmp<0.75 then floor(@numInput)+0.50
else floor(@numInput)+1
end
return @numOutput
end
go
搞掂了﹗