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'
运行结果:
OnionYang@