[原创]SQL SERVER 获得一个月有多少天 的函数例子

-- 获得一个月有多少天
-- 经典算法

CREATE  function DaysInMonth (@when datetime)
returns int
as
BEGIN
declare @rv int

if datepart(month, @when) = 2
begin
    if datepart(year, @when) % 400 = 0
    select @rv = 29
else
    if datepart(year, @when) % 100 = 0
    select @rv = 28
else
    if datepart(year, @when) % 4 = 0
    select @rv = 29
else
    select @rv = 28
end -- if
else
begin
select @rv = case (datepart(month, @when))
    when 4 then 30
    when 6 then 30
    when 9 then 30
    when 11 then 30
    else 31
    end -- case
end -- else

return @rv

END -- fn def


GO


-- 另类算法
create function GetMonths (@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

其实在MYSQL中有更简洁的表达：

 

mysql> select right(last_day(now()),2) as 'days in month';
+---------------+
| days in month |
+---------------+
| 31            |
+---------------+
1 row in set (0.00 sec)