新历转旧历

 Create Function [dbo].[fn_BitTest32](@num int,@bitpostion int)
Returns bit
As
Begin
--测试某位是否为真
Declare @Rst bit=0
Declare @bit int 
If (@bitpostion>=1 And @bitpostion<=31)
Begin 
Set @bit=Power(2,@bitpostion)
If((@num & @bit)=0)
        Set @Rst=0
     Else    
        Set @Rst=1
End
Return @Rst
End
Go

Create Function [dbo].[fn_GetChineseLeapMonth](@year int)
Returns int
Begin
--计算该年闰哪个月
Declare @info int
Select @info=convert(int,val) & 0xF from LunarDateArray
Where ID=@year-1900+1
Return @info
End
Go


Create Function [dbo].[fn_GetChineseLeapMonthDays](@year int)
Returns int
As
Begin
--计算闰月天数
Declare @info int
Declare @Rst int=0
If(dbo.fn_GetChineseLeapMonth(@year)!=0)
Begin
Select @info=convert(int,val) & 0x10000 from LunarDateArray
Where ID=@year-1900+1
If @info<>0
Set @Rst=30
Else
Set @Rst=29
End
            
  Return @Rst        
End
Go


Create Function [dbo].[fn_GetChineseMonthDays](@year int,@month int)
Returns int
As
Begin
--计算非闰月天数
Declare @info int
Declare @Rst int
Select @info=convert(int,val) & 0x0000FFFF from LunarDateArray
Where ID=@year-1900+1

if (dbo.fn_BitTest32(@info,16-@month)=1)
Set @Rst=30
Else
Set @Rst=29
Return @Rst
End
Go


Create Function [dbo].[fn_GetChineseYearDays](@year int)
Returns int
Begin
--求当年农历年天数
Declare @i int,@f int,@sumDay int,@info int,@M int=0

    Set @sumDay=348 
    Set @i=0x8000
    Select @info=convert(int,val) & 0x0FFFF from LunarDateArray
Where ID=@year-1900+1
    
    While @M<12
    Begin
Set @f=@info&@i
if (@F<>0)
Set @sumDay=@sumDay+1
Set @i=@i/2
Set @M=@M+1
    End      
    
    return @sumDay+dbo.fn_GetChineseLeapMonthDays(@year)
End
Go

Create Function [dbo].[fn_ChineseCalendar](@Date Datetime)
Returns Varchar(50)
As
Begin
--计算新历对应的旧历
Declare @Calendar Varchar(50)--旧历
Declare @leap int=0
    Declare @temp int=0
    Declare @offset int
    Declare @I int=1900
    Declare @cYear int--旧历年
    Declare @cMonth int--旧历月
    Declare @cDay int--旧历天
    Declare @cIsLeapYear bit--是否闰年
    Declare @cIsLeapMonth bit=0--是否闰月
    
if (@Date<'1900-01-01' Or @Date>'2049-12-31')
        Return null 
        
    Set @offset=DATEDIFF(dd,'1900-01-30',@Date)   
    While @I<=2050
    Begin
Set @temp=dbo.fn_GetChineseYearDays(@i)
        If(@offset-@temp<1)
           Break
        Else
          Set @offset=@offset-@temp
Set @I=@I+1 
    End
    Set @cYear=@I
    
    Set @leap=dbo.fn_GetChineseLeapMonth(@cYear)
    If(@leap>0)
Set @cIsLeapYear=1
    Else
       Set @cIsLeapYear=0

    Set @I=1
    While @I<=12
    Begin
if(@leap>0 And @i=@leap+1 And @cIsLeapMonth=0)
        Begin
          Set @cIsLeapMonth=1
          Set @i=@i-1
          Set @temp=dbo.fn_GetChineseLeapMonthDays(@cYear)
        End
        Else
        Begin
          Set @cIsLeapMonth=0
          Set @temp=dbo.fn_GetChineseMonthDays(@cYear,@i)
         End       

         Set @offset=@offset-@temp
         if(@offset<=0)
break
Set @I=@I+1
    End
    
    Set @offset=@offset+@temp
    Set @cMonth=@i
    Set @cDay=@offset
    
    Declare @A Varchar(20)='零一二三四五六七八九'
    Declare @B Varchar(100)='正,二,三,四,五,六,七,八,九,十,冬,腊'
Declare @C Varchar(20)='初十廿卅'
Declare @D Varchar(100)='日一二三四五六七八九'
Declare @tyear Varchar(4)
    Declare @ChineseYearString Varchar(10)
    Declare @ChineseMonthString Varchar(8)
    Declare @ChineseDayString Varchar(8)
   
Set @tyear=Convert(Varchar(4),@cyear)
    Set @ChineseYearString=substring(@A,convert(int,substring(@tyear,1,1))+1,1)
+substring(@A,convert(int,substring(@tyear,2,1))+1,1)
+substring(@A,convert(int,substring(@tyear,3,1))+1,1)
+substring(@A,convert(int,substring(@tyear,4,1))+1,1)
+'年'
Set @ChineseMonthString=substring(@B,@cMonth*2-1,1)+'月'

if @cday=10
Set @ChineseDayString='初十'
else if @cday=20
Set @ChineseDayString='二十'
else if @cday=30
Set @ChineseDayString='三十'
else
Set @ChineseDayString=substring(@C,@cday/10+1 ,1)+substring(@D,@cday%10+1 ,1)

    if(@cIsLeapMonth=1)
Set @Calendar='农历'+@ChineseYearString+'闰'+@ChineseMonthString+@ChineseDayString
Else
        Set @Calendar='农历'+@ChineseYearString+@ChineseMonthString+@ChineseDayString
                
    Return @Calendar  
End
Go

Create Function fn_GetGan(@Year int)
Returns Varchar(4)
As
Begin
        --计算天干 
	if @Year<4
		Return null
			
	Declare @ganStr Varchar(50)='甲乙丙丁戊己庚辛壬癸'
	Declare @zhiStr Varchar(50)='子丑寅卯辰巳午未申酉戌亥'
	
	Return Substring(@ganStr,(@Year-3)%60%10,1)+Substring(@zhiStr,(@Year-3)%60%12,1)
End
go

Create Function fn_ChineseTwentyFourDay(@Date Datetime)
Returns Varchar(10)
As
Begin
        --计算24节气 
	Declare @baseDateAndTime Datetime='1900-1-6 02:05:00'
	Declare @num numeric(38,8)
	Declare @y int,@I int=1,@J int
	Declare @newDate Datetime
	Declare @tempStr Varchar(10)
	Declare @sTermInfo Varchar(1000)='0,21208,42467,63836,85337,107014,128867,150921,173149,195551,218072,240693,263343,285989,308563,331033,353350,375494,397447,419210,440795,462224,483532,504758,'
	Declare @SolarTerm Varchar(1000)='小寒,大寒,立春,雨水,惊蛰,春分,清明,谷雨,立夏,小满,芒种,夏至,小暑,大暑,立秋,处暑,白露,秋分,寒露,霜降,立冬,小雪,大雪,冬至,'

	Set @y=year(@Date)
	While @I<=24
	Begin
		Set @J=Charindex(',',@sTermInfo)-1
		Set @num=525948.76*(@y-1900)+Convert(int,left(@sTermInfo,@j))
		Set @newDate=DATEADD(MINUTE ,@num,@baseDateAndTime)
		if (Datepart(dayofyear,@newDate)=Datepart(dayofyear,@Date))
		Begin
			Set @tempStr=left(@SolarTerm,2)
			Break
		End	
		Else
		Begin
			Set @sTermInfo=Stuff(@sTermInfo,1,@J+1,'')
			Set @SolarTerm=Stuff(@SolarTerm,1,3,'')
		End	
		Set @I=@I+1
	End
	Return @tempStr
End
go              
  
--测试
Select dbo.fn_ChineseCalendar(GETDATE())