27,580
社区成员
发帖
与我相关
我的任务
分享
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