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？？
[解决办法]
发现问题了，用这句：
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
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
