使用SQL函数
1 USE RDS 2 GO 3 4 --For ticket escaluation function. 5 insert into SystemSettings(Name,Value) VALUES('MaxBatchSize','10') 6 7 GO 8 9 10 /****** 对象: UserDefinedFunction [dbo].[CalcInterVal] 脚本日期: 07/04/2008 11:46:26 ******/ 11 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CalcInterVal]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) 12 DROP FUNCTION [dbo].[CalcInterVal] 13 GO 14 /****** 对象: UserDefinedFunction [dbo].[CalcInterVal] 脚本日期: 07/04/2008 11:46:26 ******/ 15 SET ANSI_NULLS ON 16 GO 17 SET QUOTED_IDENTIFIER ON 18 GO 19 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CalcInterVal]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) 20 BEGIN 21 execute dbo.sp_executesql @statement = N' 22 CREATE FUNCTION [dbo].[CalcInterVal](@datepart varchar(20),@startdate varchar(50), @enddate varchar(50)) 23 returns decimal 24 BEGIN 25 if Datepart("minute",@startdate) > 0 OR Datepart("second",@startdate) > 0 OR Datepart("millisecond",@startdate) > 0 26 BEGIN 27 SET @startdate = Dateadd("hh",1,@startdate) 28 END 29 30 return DateDiff("hh",@startdate,@enddate) 31 END ' 32 END 33 34 GO
