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('01','2020') as '1',     
          dbo.fn_GetDayofMonth_1('02','2020') as '2',
          dbo.fn_GetDayofMonth_1('03','2020') as '3',
          dbo.fn_GetDayofMonth_1('04','2020') as '4',
          dbo.fn_GetDayofMonth_1('05','2020') as '5',
          dbo.fn_GetDayofMonth_1('06','2020') as '6',
          dbo.fn_GetDayofMonth_1('07','2020') as '7',
          dbo.fn_GetDayofMonth_1('08','2020') as '8',
          dbo.fn_GetDayofMonth_1('09','2020') as '9',
          dbo.fn_GetDayofMonth_1('10','2020') as '10',
          dbo.fn_GetDayofMonth_1('11','2020') as '11',
          dbo.fn_GetDayofMonth_1('12','2020') as '12'

运行结果：

 

 

--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('202001') as '1',
          dbo.fn_GetDayofMonth_2('202002') as '2',
          dbo.fn_GetDayofMonth_2('202003') as '3',
          dbo.fn_GetDayofMonth_2('202004') as '4',
          dbo.fn_GetDayofMonth_2('202005') as '5',
          dbo.fn_GetDayofMonth_2('202006') as '6',
          dbo.fn_GetDayofMonth_2('202007') as '7',
          dbo.fn_GetDayofMonth_2('202008') as '8',
          dbo.fn_GetDayofMonth_2('202009') as '9',
          dbo.fn_GetDayofMonth_2('202010') as '10',
          dbo.fn_GetDayofMonth_2('202011') as '11',
          dbo.fn_GetDayofMonth_2('202012') as '12'

运行结果：