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
--传回农历年的总天数
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 solardata 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
set @mleap1= @mleap
--传回农历年闰月的天数
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=ltrim(@offset)
--输出日期
set @outputdate=@year+'-'+@month+'-'+@day
return @outputdate
end
GO
--生成1900至2045年农历与公历对照表
if object_id('LunarCalenderContrastTable') is not null
drop table LunarCalenderContrastTable
GO
create table LunarCalenderContrastTable
(
Calender datetime,
Lunar datetime
)
GO
BEGIN
declare @i int
declare @day nvarchar(10)
set @i= DATEDIFF(day,'1900-1-1','2020-1-1')
while @i>0
begin
set @day=dateadd(d,@i,'1900-1-1')
insert into LunarCalenderContrastTable(Calender,Lunar) values(@day,dbo.Proc_ConvertLunar(@day))
set @i=@i-1
end
END
GO
--存储过程查询生日
if object_id('GetCalender') is not null
drop function GetCalender
go
create function dbo.GetCalender(
@month varchar(3),
@day varchar(3),
@now datetime)
returns datetime
as
begin
declare @years varchar(10)
declare @dates varchar(12)
set @years= year(@now)
set @dates=@years+'-'+@month+'-'+@day
return(select Calender from LunarCalenderContrastTable where Lunar =@dates)
end
GO
--测试
select dbo.GetCalender(2,7,getdate())
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
decl