公历日期转农历日期的SQL函数

/*-------------------------------------------------------------------------------
功能:
     返回指定日期的农历日期
     (需配合 sys_date_lunar 表使用)
参数说明:
     @solarDay   日期
     @type   日期: 0: 年月日
             文本: 1: 月日的文本
             数字: 20: ymd , 21 md
例:
    select dbo.usf_GetDateLunar('2023-12-01',0)        返回:  2023-10-19

    select dbo.usf_GetDateLunar('2023-12-01',1)        返回:  十月十九

    select dbo.usf_GetDateLunar('2023-12-01',20)    返回:  20231019

    select dbo.usf_GetDateLunar('2023-12-01',21)    返回:  1019
-------------------------------------------------------------------------------------*/

create   FUNCTION   [dbo].[usf_GetDateLunar](   
   @solarDay  as DATETIME , @type as tinyint = 0)       
   /* @type 返回格式

   */
 RETURNS   varchar(200)
 AS           
 BEGIN
   --转自CSDN 
     DECLARE   @solData   int           
     DECLARE   @offset   int           
     DECLARE   @iLunar   int           
     DECLARE   @i   INT             
     DECLARE   @j   INT             
     DECLARE   @yDays   int           
     DECLARE   @mDays   int           
     DECLARE   @mLeap   int           
     DECLARE   @mLeapNum   int           
     DECLARE   @bLeap   smallint           
     DECLARE   @temp   int           
           
     DECLARE   @YEAR   INT             
     DECLARE   @MONTH   INT           
     DECLARE   @DAY   INT           
               
     DECLARE   @OUTPUTDATE varchar(100)   
       
     --保证传进来的日期是不带时间           
     SET   @solarDay=cast(@solarDay   AS   char(10))           
     SET   @offset=CAST(@solarDay-'1900-01-30'   AS   INT)       
       
           
     --确定农历年开始           
     SET   @i=1900           
     --SET   @offset=@solData           
     WHILE   @i<2050   AND   @offset>0           
     BEGIN           
         SET   @yDays=348           
         SET   @mLeapNum=0           
         SELECT   @iLunar=dataInt   FROM   sys_date_lunar   WHERE   yearId=@i           
           
         --传回农历年的总天数           
         SET   @j=32768           
         WHILE   @j>8           
         BEGIN           
             IF   @iLunar   &   @j   >0           
                 SET   @yDays=@yDays+1           
             SET   @j=@j/2           
         END           
           
         --传回农历年闰哪个月   1-12   ,   没闰传回   0           
         SET   @mLeap   =   @iLunar   &   15           
           
         --传回农历年闰月的天数   ,加在年的总天数上           
         IF   @mLeap   >   0           
         BEGIN           
             IF   @iLunar   &   65536   >   0           
                 SET   @mLeapNum=30           
             ELSE             
                 SET   @mLeapNum=29           
           
             SET   @yDays=@yDays+@mLeapNum           
         END           
                   
         SET   @offset=@offset-@yDays           
         SET   @i=@i+1           
     END           
               
     IF   @offset   <=   0           
     BEGIN           
         SET   @offset=@offset+@yDays           
         SET   @i=@i-1           
     END           
     --确定农历年结束               
     SET   @YEAR=@i           
       
     --确定农历月开始           
     SET   @i   =   1           
     SELECT   @iLunar=dataInt   FROM   sys_date_lunar   WHERE   yearId=@YEAR       
       
     --判断那个月是润月           
     SET   @mLeap   =   @iLunar   &   15           
     SET   @bLeap   =   0         
       
     WHILE   @i   <   13   AND   @offset   >   0           
     BEGIN           
         --判断润月           
         SET   @mDays=0           
         IF   (@mLeap   >   0   AND   @i   =   (@mLeap+1)   AND   @bLeap=0)           
         BEGIN--是润月           
             SET   @i=@i-1           
             SET   @bLeap=1           
             --传回农历年闰月的天数           
             IF   @iLunar   &   65536   >   0           
                 SET   @mDays   =   30           
             ELSE             
                 SET   @mDays   =   29           
         END           
         ELSE           
         --不是润月           
         BEGIN           
             SET   @j=1           
             SET   @temp   =   65536             
             WHILE   @j<=@i           
             BEGIN           
                 SET   @temp=@temp/2           
                 SET   @j=@j+1           
             END           
           
             IF   @iLunar   &   @temp   >   0           
                 SET   @mDays   =   30           
             ELSE           
                 SET   @mDays   =   29           
         END           
               
         --解除闰月       
         IF   @bLeap=1   AND   @i=   (@mLeap+1)       
             SET   @bLeap=0       
       
         SET   @offset=@offset-@mDays           
         SET   @i=@i+1           
     END           
           
     IF   @offset   <=   0           
     BEGIN           
         SET   @offset=@offset+@mDays           
         SET   @i=@i-1           
     END           
       
     --确定农历月结束               
     SET   @MONTH=@i       
           
     --确定农历日结束      
     SET   @DAY=@offset    
     
     /*
     if   @bLeap=1     
     SET   @OUTPUTDATE=(CAST(@YEAR   AS   VARCHAR(4))+'-闰'+CAST(@MONTH   AS   VARCHAR(2))+'-'+CAST(@DAY   AS   VARCHAR(2)))   
     else   
     SET   @OUTPUTDATE=(CAST(@YEAR   AS   VARCHAR(4))+'-'+CAST(@MONTH   AS   VARCHAR(2))+'-'+CAST(@DAY   AS   VARCHAR(2)))   

     */
      SET   @OUTPUTDATE=
         case @type
            when 1 then 
                case when @bLeap=1  then '闰' else '' end  
                + case   @MONTH  when 1 then '正月' when 2 then '二月' when 3 then '三月' when 4 then '四月' when 5 then '五月' when 6 then '六月' when 7 then '七月' 
                        when 8 then '八月' when 9 then '九月' when 10 then '十月' when 11 then '十一月' when 12 then '腊月'  else '出错' end
                + case @DAY when 1 then '初一' when 2 then '初二'when 3 then '初三'when 4 then '初四'when 5 then '初五' 
                    when 6 then '初六' when 7 then '初七'when 8 then '初八'when 9 then '初九'when 10 then '初十' 
                    when 11 then '十一' when 12 then '十二'when 13 then '十三'when 14 then '十四'when 15 then '十五' 
                    when 16 then '十六' when 17 then '十七'when 18 then '十八'when 19 then '十九'when 20 then '廿十' 
                    when 21 then '廿一' when 22 then '廿二'when 23 then '廿三'when 24 then '廿四'when 25 then '廿五' 
                    when 26 then '廿六' when 27 then '廿七'when 28 then '廿八'when 29 then '廿九'when 30 then '三十'
                    else '出错' end
            when 20 then CONVERT(varchar(10), @YEAR *10000 + @MONTH*100 +@DAY)
            when 21 then CONVERT(varchar(10),   @MONTH*100 +@DAY)
            else  convert(varchar(10), convert(datetime, (CAST(@YEAR   AS   VARCHAR(4))+'-'+  CAST(@MONTH   AS   VARCHAR(2))+'-' + CAST(@DAY   AS   VARCHAR(2)))  ),120)
         end

     RETURN   @OUTPUTDATE       
 END
GO