计算指定年月的最后一天的自定义函数-数据库专栏,SQL Server

/*
    name :    fun_yiyuedezuihouyitian
    function :   根据传入的格式如“200311”的年月值计算出当月的最后一天
    parameters :   
                        benyue  本月
                            type :  char(6)  
                            format :  “200311”
    returnvalue :   
                        格式如“200310”的年月值
                            type :  char(6)
    steps :
    author :   waxdoll cheung
    date :    2004-04-04
*/

create function dbo.fun_yiyuedezuihouyitian
 (
  @benyue char(6)
 )
returns char(10)
as
 begin
  declare @returnvalue char(2)
  declare @yuefen char(2)
  declare @nianfen char(4)

  set @yuefen = right(@benyue, 2)
  set @nianfen = left(@benyue, 4)

  if @yuefen in (01, 03, 05, 07, 08, 10, 12)
  begin
   set @returnvalue = 31
  end

  if @yuefen in (04, 06, 09, 11)
  begin
   set @returnvalue = 30
  end

  if @yuefen = 02
  begin
   if ((@nianfen % 4 = 0 and @nianfen % 100 <> 0) or (@nianfen % 400 = 0))
   begin
    set @returnvalue = 29
   end
   else
   begin
    set @returnvalue = 28
   end
  end

  return @nianfen + – + @yuefen + – + @returnvalue
 end