今天查看以前专案的数据库自定义函数,温习温习。发现其中有一个函数,是获取月份的天数的自定义函数。如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE
FUNCTION
[
dbo
]
.
[
udf_DaysInMonth
]
(
@Date DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @dim AS TABLE (M INT ,Dy INT )
INSERT INTO @dim VALUES
( 1 , 31 ),( 3 , 31 ),( 5 , 31 ),( 7 , 31 ),( 8 , 31 ),( 10 , 31 ),( 12 , 31 ),
( 4 , 30 ),( 6 , 30 ),( 9 , 30 ),( 11 , 30 ),
( 2 ,
CASE WHEN ( YEAR ( @Date ) % 4 = 0 AND YEAR ( @Date ) % 100 <> 0 ) OR ( YEAR ( @Date ) % 400 = 0 )
THEN 29
ELSE 28 END
)
DECLARE @RValue INT
SELECT @RValue = [ Dy ] FROM @dim WHERE [ M ] = MONTH ( @Date )
RETURN @RValue
END
GO
(
@Date DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @dim AS TABLE (M INT ,Dy INT )
INSERT INTO @dim VALUES
( 1 , 31 ),( 3 , 31 ),( 5 , 31 ),( 7 , 31 ),( 8 , 31 ),( 10 , 31 ),( 12 , 31 ),
( 4 , 30 ),( 6 , 30 ),( 9 , 30 ),( 11 , 30 ),
( 2 ,
CASE WHEN ( YEAR ( @Date ) % 4 = 0 AND YEAR ( @Date ) % 100 <> 0 ) OR ( YEAR ( @Date ) % 400 = 0 )
THEN 29
ELSE 28 END
)
DECLARE @RValue INT
SELECT @RValue = [ Dy ] FROM @dim WHERE [ M ] = MONTH ( @Date )
RETURN @RValue
END
GO
获取月份天数,以前在博客上也有写过,不过它只是取得二月份的天数。链接如下:http://www.cnblogs.com/insus/articles/2025019.html
现第一眼看见专案中这个函数,总觉它写得不够好的感觉,是否能把它改写得更好些,启发点也是从获取二月份天数的CASE函数想起的。
因此,我尝试改了,如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE
FUNCTION
[
dbo
]
.
[
udf_DaysInMonth
]
(
@Date DATETIME
)
RETURNS INT
AS
BEGIN
RETURN CASE WHEN MONTH ( @Date ) IN ( 1 , 3 , 5 , 7 , 8 , 10 , 12 ) THEN 31
WHEN MONTH ( @Date ) IN ( 4 , 6 , 9 , 11 ) THEN 30
ELSE CASE WHEN ( YEAR ( @Date ) % 4 = 0 AND YEAR ( @Date ) % 100 <> 0 ) OR ( YEAR ( @Date ) % 400 = 0 )
THEN 29
ELSE 28
END
END
END
(
@Date DATETIME
)
RETURNS INT
AS
BEGIN
RETURN CASE WHEN MONTH ( @Date ) IN ( 1 , 3 , 5 , 7 , 8 , 10 , 12 ) THEN 31
WHEN MONTH ( @Date ) IN ( 4 , 6 , 9 , 11 ) THEN 30
ELSE CASE WHEN ( YEAR ( @Date ) % 4 = 0 AND YEAR ( @Date ) % 100 <> 0 ) OR ( YEAR ( @Date ) % 400 = 0 )
THEN 29
ELSE 28
END
END
END
如果你已经有引过Insus.NET那个获取二月份天数的自定义函数,也可以参考下面这个版本:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE
FUNCTION
[
dbo
]
.
[
udf_DaysInMonth
]
(
@Date DATETIME
)
RETURNS INT
AS
BEGIN
RETURN CASE WHEN MONTH ( @Date ) IN ( 1 , 3 , 5 , 7 , 8 , 10 , 12 ) THEN 31
WHEN MONTH ( @Date ) IN ( 4 , 6 , 9 , 11 ) THEN 30
ELSE [ dbo ] . [ DaysOfFebruary ] ( YEAR ( @Date ))
END
END
(
@Date DATETIME
)
RETURNS INT
AS
BEGIN
RETURN CASE WHEN MONTH ( @Date ) IN ( 1 , 3 , 5 , 7 , 8 , 10 , 12 ) THEN 31
WHEN MONTH ( @Date ) IN ( 4 , 6 , 9 , 11 ) THEN 30
ELSE [ dbo ] . [ DaysOfFebruary ] ( YEAR ( @Date ))
END
END