SQL内置函数
datediff(hh,date1,date2) 用于返回两个日期之间的小时数。不能直接返回 HH:mm:ss 格式的时间差
所以自己写了个计算时间差的函数,请大家多多指教
CREATE
function
getbetweentime(
@rq1
datetime
,
@rq2
datetime
)
returns varchar ( 20 ) as
begin
declare @sumtime int
declare @timestr varchar ( 10 )
if @rq2 > @rq1
set @sumtime = datediff (ss, @rq1 , @rq2 )
else set @sumtime = 99999
if @sumtime = 99999
set @timestr = ' error ' -- 如果@rq2小于@rq1判断为错误数据(用于特定的时间规则,校验用户的时间输入是否正确)
else if @sumtime > 3600
begin
set @timestr = convert ( varchar ( 10 ), @sumtime / 3600 ) + ' : '
set @sumtime = @sumtime % 3600
set @timestr = @timestr + convert ( varchar ( 10 ), @sumtime / 60 ) + ' : ' + convert ( varchar ( 10 ), @sumtime % 60 )
end
else if ( @sumtime < 3600 ) and ( @sumtime > 60 )
set @timestr = convert ( varchar ( 10 ), @sumtime / 60 ) + ' : ' + convert ( varchar ( 10 ), @sumtime % 60 )
else if ( @sumtime < 60 )
set @timestr = ' 0: ' + convert ( varchar ( 10 ), @sumtime )
return @timestr
end
returns varchar ( 20 ) as
begin
declare @sumtime int
declare @timestr varchar ( 10 )
if @rq2 > @rq1
set @sumtime = datediff (ss, @rq1 , @rq2 )
else set @sumtime = 99999
if @sumtime = 99999
set @timestr = ' error ' -- 如果@rq2小于@rq1判断为错误数据(用于特定的时间规则,校验用户的时间输入是否正确)
else if @sumtime > 3600
begin
set @timestr = convert ( varchar ( 10 ), @sumtime / 3600 ) + ' : '
set @sumtime = @sumtime % 3600
set @timestr = @timestr + convert ( varchar ( 10 ), @sumtime / 60 ) + ' : ' + convert ( varchar ( 10 ), @sumtime % 60 )
end
else if ( @sumtime < 3600 ) and ( @sumtime > 60 )
set @timestr = convert ( varchar ( 10 ), @sumtime / 60 ) + ' : ' + convert ( varchar ( 10 ), @sumtime % 60 )
else if ( @sumtime < 60 )
set @timestr = ' 0: ' + convert ( varchar ( 10 ), @sumtime )
return @timestr
end