UDF_获取某年某月有多少天

UDF

--获取某年某月有多少天

--drop function fn_GetDayofMonth_1

/*

    HLERP ( [dbo].[GetMonths]  )

*/

go

create  function dbo.fn_GetDayofMonth_1 (@Month smallint, @Year int)

returns smallint

/*获取当月的天数*/

as

begin

     RETURN( Day(Dateadd(dd, -1, Dateadd(m, 1, Cast(@Year AS VARCHAR(4)) + '-'

                                               + Cast(@Month AS VARCHAR(2)) + '-01'))) )

end

select dbo.fn_GetDayofMonth_1('','') as '',

          dbo.fn_GetDayofMonth_1('','') as '',

          dbo.fn_GetDayofMonth_1('','') as '',

          dbo.fn_GetDayofMonth_1('','') as '',

          dbo.fn_GetDayofMonth_1('','') as '',

          dbo.fn_GetDayofMonth_1('','') as '',

          dbo.fn_GetDayofMonth_1('','') as '',

          dbo.fn_GetDayofMonth_1('','') as '',

          dbo.fn_GetDayofMonth_1('','') as '',

          dbo.fn_GetDayofMonth_1('','') as '',

          dbo.fn_GetDayofMonth_1('','') as '',

          dbo.fn_GetDayofMonth_1('','') as ''

运行结果：

--drop function fn_GetMonthDayAll

/*  参考：  http://www.maomao365.com/?p=6792&sourceInfo=blog&dateInfo=20181009

    制作思路:

    1. 获取指定月份的第一天，

    2. 并采用dateadd向后加一个月形成一个新的日期

    3. 将两个日期相减，并返回之间的相差天数，作为指定月份的总天数，如下所示:

*/

Create function dbo.fn_GetDayofMonth_2(@YYYYMM varchar(20))

returns int

as

begin

    declare @dayAll int

    declare @monthStartDay datetime

    set @monthStartDay = left(@yyyyMM,4) + '-'+ right(@YYYYMM,2) + '-01'

    SELECT @dayAll = DATEDIFF(DAY, @monthStartDay, DATEADD(MONTH,1,@monthStartDay))

    return @dayAll

end

select dbo.fn_GetDayofMonth_2('') as '',

          dbo.fn_GetDayofMonth_2('') as '',

          dbo.fn_GetDayofMonth_2('') as '',

          dbo.fn_GetDayofMonth_2('') as '',

          dbo.fn_GetDayofMonth_2('') as '',

          dbo.fn_GetDayofMonth_2('') as '',

          dbo.fn_GetDayofMonth_2('') as '',

          dbo.fn_GetDayofMonth_2('') as '',

          dbo.fn_GetDayofMonth_2('') as '',

          dbo.fn_GetDayofMonth_2('') as '',

          dbo.fn_GetDayofMonth_2('') as '',

          dbo.fn_GetDayofMonth_2('') as ''

运行结果：