SQL 获取两个时间钟头部分之差
SQL 获取两个时间小时部分之差
create function gethours(@stime datetime,@etime datetime)
returns int
as
begin
declare @hstime nvarchar(30)
declare @hetime nvarchar(30)
declare @result int
select @hstime=Ltrim(datepart(hh,@stime))
select @hetime=Ltrim(datepart(hh,@etime))
if (@hstime<8 and @hetime<17 and @hetime>7)
begin
set @result=@hetime-8
end
else if (@hstime<8 and @hetime>17)
begin
set @result=8
end
else if (@hstime<8 and @hetime<8)
begin
set @result=0
end
else if (@hstime>=17 and @hetime>=17)
begin
set @result=0
end
else if (@hstime>=17 and @hetime<17 and @hetime>=8)
begin
set @result=@hetime-8
end
else if (@hstime>=17 and @hetime<8)
begin
set @result=0
end
else if (@hstime>=8 and @hstime<17 and @hetime>=17)
begin
set @result=17-@hstime
end
else if (@hstime>=8 and @hstime<=17 and @hetime<8)
begin
set @result=17-@hstime
end
else if (@hstime>@hetime AND @hetime>=8 AND @hstime <17)
begin
set @result=cast(@hetime as int)-cast(@hstime as int)+9
end
else if (@hstime>@hetime AND @hetime>=8 AND @hstime >17)
begin
set @result=cast(@hetime as int)-8
end
else
begin
set @result=(cast(@hetime as int)- cast(@hstime as int))
end
return @result
end
我执行这个SQL的时候,为什么只是返回else中的结果,我在满足(@hstime>@hetime AND @hetime>=8 AND @hstime <17
的条件后,例如 stime:‘2013-12-06 16:22:28.840’ etime: ‘2013-12-11 08:39:39.270’ 返回的是-8??
------解决方案--------------------
发现问题了,用这句:
create function gethours(@stime datetime,@etime datetime)
returns int
as
begin
declare @hstime nvarchar(30)
declare @hetime nvarchar(30)
declare @result int
select @hstime=Ltrim(datepart(hh,@stime))
select @hetime=Ltrim(datepart(hh,@etime))
if (@hstime<8 and @hetime<17 and @hetime>7)
begin
set @result=@hetime-8
end
else if (@hstime<8 and @hetime>17)
begin
set @result=8
end
else if (@hstime<8 and @hetime<8)
begin
set @result=0
end
else if (@hstime>=17 and @hetime>=17)
begin
set @result=0
end
else if (@hstime>=17 and @hetime<17 and @hetime>=8)
begin
set @result=@hetime-8
end
else if (@hstime>=17 and @hetime<8)
begin
set @result=0
end
else if (@hstime>=8 and @hstime<17 and @hetime>=17)
begin
set @result=17-@hstime
end
else if (@hstime>=8 and @hstime<=17 and @hetime<8)
begin
set @result=17-@hstime
end
else if (@hstime>@hetime AND @hetime>=8 AND @hstime <17)
begin
set @result=cast(@hetime as int)-cast(@hstime as int)+9
end
else if (@hstime>@hetime AND @hetime>=8 AND @hstime >17)
begin
set @result=cast(@hetime as int)-8
end
else
begin
set @result=(cast(@hetime as int)- cast(@hstime as int))
end
return @result
end
我执行这个SQL的时候,为什么只是返回else中的结果,我在满足(@hstime>@hetime AND @hetime>=8 AND @hstime <17
的条件后,例如 stime:‘2013-12-06 16:22:28.840’ etime: ‘2013-12-11 08:39:39.270’ 返回的是-8??
------解决方案--------------------
发现问题了,用这句:
alter FUNCTION gethours
(
@stime DATETIME ,
@etime DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @hstime INT--NVARCHAR(30)
DECLARE @hetime INT--NVARCHAR(30)
DECLARE @result INT
SELECT @hstime = LTRIM(DATEPART(hh, @stime))
SELECT @hetime = LTRIM(DATEPART(hh, @etime))
IF ( @hstime < 8
AND @hetime < 17
AND @hetime > 7
)
BEGIN
SET @result = @hetime - 8
END
ELSE
IF ( @hstime < 8
AND @hetime > 17
)
BEGIN
SET @result = 8
END