SQL Lunar transformation function (display Chinese format, add run month display)

SQL Lunar transformation function (display Chinese format, add run month display)

Create a lunar date function
If object_id (' Fn_getlunar ') is not null
Drop function Fn_getlunar
Go
Create function Dbo.fn_getlunar (@solarday datetime)
Returns nvarchar (30)
As
Begin
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 @mleap1 int
DECLARE @mleapnum int
DECLARE @bleap smallint
DECLARE @temp int
declare @year nvarchar (10)
declare @month nvarchar (10)
declare @day nvarchar (10)
declare @chinesenum nvarchar (10)
declare @outputdate nvarchar (30)
Set @offset =datediff (day, ' 1900-01-30 ', @solarday)
--Determine the beginning of the lunar year
Set @i=1900
--set @offset = @soldata
While @i<2050 and @offset >0
Begin
Set @ydays =348
Set @mleapnum =0
Select @ilunar =dataint from Solardata where yearid=@i

--The total number of days returned to the Lunar New Year
Set @j=32768
While @j>8
Begin
If @ilunar & @j >0
Set @ydays = @ydays +1
Set @j=@j/2
End
--Return to the Lunar New Year 1-12, no leap back to 0
Set @mleap = @ilunar & 15
--Returns the number of days of the Lunar New Year leap, plus the total number of days in the year
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
--Determine the end of the lunar calendar year
Set @year =@i
--Determine the beginning of lunar month
Set @i = 1
Select @ilunar =dataint from Solardata where yearid= @year
--judging that month is run month
Set @mleap = @ilunar & 15
Set @bleap = 0
While @i < @offset > 0
Begin
--Judge Run Moon
Set @mdays =0
if (@mleap > 0 and @i = (@mleap + 1) and @bleap =0)
begin--is run month
Set @i=@i-1
Set @bleap =1
Set @mleap1 = @mleap
--Returns the number of days in the Lunar New Year Leap
If @ilunar & 65536 > 0
Set @mdays = 30
Else
Set @mdays = 29
End
Else
-Not run the moon
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

--Release run month
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

--Determine the end of lunar month
Set @month =@i

--Determine the end of the lunar day
Set @day =ltrim (@offset)
--Output date
Set @chinesenum =n ' 1,234,567,890 '
While Len (@year) >0
Select @outputdate =isnull (@outputdate, ")
+ substring (@chinesenum, left (@year, 1) +1,1)
, @year =stuff (@year, 1, 1, "")
Set @outputdate = @outputdate +n ' year '
+ Case @mleap1 When @month then n ' run ' Else ' end
If cast (@month as int) <10
Set @outputdate = @outputdate
+ Case @month When 1 then n ' positive '
else substring (@chinesenum, left (@month, 1) +1,1)
End
else if cast (@month as int) >=10
Set @outputdate = @outputdate
+ Case @month when ' Then ' n ' ten ' when one then n ' 11 '
else n ' 12 ' end
Set @outputdate = @outputdate + n ' month '
If cast (@day as int) <10
Set @outputdate = @outputdate + n ' early '
+ substring (@chinesenum, left (@day, 1) +1,1)
else if @day between ' and ' 19 '
Set @outputdate = @outputdate
+ Case @day when ' then n ' decade ' else N ' ten ' +
SUBSTRING (@chinesenum, right (@day, 1) +1,1) end
else if @day between ' and ' 29 '
Set @outputdate = @outputdate
+ Case @day when ' then n ' 20 ' else n ' 20 ' end
+ Case @day when ' then n ' else
SUBSTRING (@chinesenum, right (@day, 1) +1,1) end
Else
Set @outputdate = @outputdate +n ' 30 '
Return @outputdate
End
Go
Select Dbo.fn_getlunar (getdate ()) as [adaptation date (lunar calendar)],
GETDATE () as [adaptation date (Gregorian calendar)]
/*
Adaptation date (lunar calendar) adaptation date (Gregorian)
------------------    -----------------------
March 2010 12,020-04-24 07:01:17.340

(1 rows affected)
*/

Select CONVERT (char, DATEADD (D,number, ' 2008-1-1 '),) as Gregorian,
     Dbo.fn_getlunar ( DATEADD (D,number, ' 2008-1-1 ')) as the lunar calendar
from master. Spt_values
where type= ' P '
/*
Gregorian          lunar
----------------- -----------------------
2008-01-01 November 2007 23
2008-01-02 November 2007 24
2008-01-03 November 2007 25
2008-01-04 November 2007 26
2008-01-05 November 2007 27
2008-01-06 November 2007 28
2008-01-07 November 2007 29
2008-01-08 December 2007 First
2008-01-09 the beginning of December 2007, two
2008-01-10, December 2007, third
...
2013-07-31 June 2013 24
2013-08-01 June 2013 25
2013-08-02 June 2013 26
2013-08-03 June 2013 27
2013-08-04 June 2013 28
2013-08-05 June 2013 29
2013-08-06 June 2013 30
2013-08-07 July 2013 First
2013-08-08 July 2013 early two
2013-08-09 July 2013 Early three