ALTER FUNCTION [dbo].[fnGetDayCountOfMonth]
(@nYear int ,@nMonth int)
RETURNS varchar(16)
AS
BEGIN
declare @nCount int
declare @dt smalldatetime
set @nMonth=@nMonth +1
if(@nMonth = 13)
set @nMonth = 1
set @dt = cast(@nYear as varchar(4))+'-'+cast(@nMonth as varchar(2))+'-1'
set @nCount = datepart(day,@dt - 1) return @nCount
END
(@nYear int ,@nMonth int)
RETURNS varchar(16)
AS
BEGIN
declare @nCount int
declare @dt smalldatetime
set @nMonth=@nMonth +1
if(@nMonth = 13)
set @nMonth = 1
set @dt = cast(@nYear as varchar(4))+'-'+cast(@nMonth as varchar(2))+'-1'
set @nCount = datepart(day,@dt - 1) return @nCount
END
ALTER FUNCTION [dbo].[fnGetDayCountOfMonth]
(@nYear int ,@nMonth int) --传入两个int类型的参数
RETURNS varchar(16) --返回一个varchar类型的结果
AS
BEGIN
declare @nCount INT --定义一个int类型的变量
declare @dt SMALLDATETIME --定义一个smalldatetime类型的变量
set @nMonth=@nMonth +1 --变量@nMonth加1
if(@nMonth = 13) --如果@nCount=13,设置@nMonth=1(这里面的判断不够严密)
set @nMonth = 1 --如果上面通过,则@nMonth=1,不通过不走这里
set @dt = cast(@nYear as varchar(4))+'-'+cast(@nMonth as varchar(2))+'-1' --设置@dt=传入参数@nYear+'-'+@nMonth+'-'+1;最后形成的格式如2019-9-1;注意这里的月份已经是你传入参数@nMonth加上1之后的月份。
set @nCount = datepart(day,@dt - 1) --使用datepart函数获取到上一行@dt减去一天后得到的日期,比如上面是2019-9-1;减去一天后就是2019-8-31;在这个日期基础上获取这个日期的日的值,也就是31。从而得到这个月的天数。return @nCount --返回这个天数
END
--个人感觉这个函数写的不是很严谨,还有改善的余地。
SELECT dbo.fnGetDayCountOfMonth(2018,5);----------------
31(1 行受影响)