本文转载自微信公众号《SQL数据库开发》作者平凡世界转载请联系SQL数据库开发公众号.之前用存储过程发过一个指定年份的日历表《SQL如何制作一张日历维度表》，然后在文末留下了一个思考题：如何将日期对应的农历添加到日历表中？很多同学留言说怎么解决农历问题，今天我就来告诉大家怎么做。阴历不同于世界通用的历法。这是科学家计算出来的。目前为止最多只有2049个，以后可以追加！所以我们可以把计算出来的具体农历做成一个表，通过调用当前的日期来返回具体的农历。创建农历表CREATETABLESolarData(yearIdintnotnullprimarykey,datachar(7)notnull,dataIntintnotnull)插入农历数据农历数据包含从1900年到2049年的具体数据。INSERTINTOSolarDataSELECT1900,'0x04bd8',19416UNIONALLSELECT1901,'0x04ae0',19168UNIONALLSELECT1902,'0x0a570',42352UNIONALLSELECT1903,'0x054d5',21717UNIONALLSELECT1904,'0x0d260',53856UNIONALLSELECT1905,'0x0d950',55632UNIONALLSELECT1906,'0x16554',91476UNIONALLSELECT1907,'0x056a0',22176UNIONALLSELECT1908,'0x09ad0',39632UNIONALLSELECT1909,'0x055d2',21970UNIONALLSELECT1910,'0x04ae0',19168UNIONALLSELECT1911,'0x0a5b6',42422UNIONALLSELECT1912,'0x0a4d0',42192UNIONALLSELECT1913,'0x0d250',53840UNIONALLSELECT1914,'0x1d255',119381UNIONALLSELECT1915,'0x0b540',46400UNIONALLSELECT1916,'0x0d6a0',54944UNIONALLSELECT1917,'0x0ada2',44450UNIONALLSELECT1918,'0x095b0',38320UNIONALLSELECT1919,'0x14977',84343UNIONALLSELECT1920,'0x04970',18800UNIONALLSELECT1921,'0x0a4b0',42160UNIONALLSELECT1922,'0x0b4b5',46261UNIONALLSELECT1923,'0x06a50',27216UNIONALLSELECT1924,'0x06d40',27968UNIONALLSELECT1925,'0x1ab54',109396UNIONALLSELECT1926,'0x02b60',11104UNIONALLSELECT1927,'0x09570',38256UNIONALLSELECT1928,'0x052f2',21234UNIONALLSELECT1929,'0x04970',18800UNIONALLSELECT1930,'0x06566',25958UNIONALLSELECT1931,'0x0d4a0',54432UNIONALLSELECT1932,'0x0ea50',59984UNIONALLSELECT1933,'0x06e95',28309UNIONALLSELECT1934,'0x05ad0',23248UNIONALLSELECT1935,'0x02b60',11104UNIONALLSELECT1936,'0x186e3',100067UNIONALLSELECT1937,'0x092e0',37600UNIONALLSELECT1938,'0x1c8d7',116951UNIONALLSELECT1939,'0x0c950',51536UNIONALLSELECT1940,'0x0d4a0',54432UNIONALLSELECT1941,'0x1d8a6',120998UNIONALLSELECT1942,'0x0b550',46416UNIONALLSELECT1943,'0x056a0',22176UNIONALLSELECT1944,'0x1a5b4',107956UNIONALLSELECT1945,'0x025d0',9680UNIONALLSELECT1946,'0x092d0',37584UNIONALLSELECT1947,'0x0d2b2',53938UNIONALLSELECT1948,'0x0a950',43344UNIONALLSELECT1949,'0x0b557',46423UNIONALLSELECT1950,'0x06ca0',27808UNIONALLSELECT1951,'0x0b550',46416UNIONALLSELECT1952,'0x15355',86869UNIONALLSELECT1953,'0x04da0',19872UNIONALLSELECT1954,'0x0a5d0',42448UNIONALLSELECT1955,'0x14573',83315UNIONALLSELECT1956,'0x052d0',21200UNIONALLSELECT1957,'0x0a9a8',43432UNIONALLSELECT1958,'0x0e950',59728UNIONALLSELECT1959,'0x06aa0',27296UNIONALLSELECT1960,'0x0aea6',44710UNIONALLSELECT1961,'0x0ab50',43856UNIONALLSELECT1962,'0x04b60',19296UNIONALLSELECT1963,'0x0aae4',43748UNIONALLSELECT1964,'0x0a570',42352UNIONALLSELECT1965,'0x05260',21088UNIONALLSELECT1966,'0x0f263',62051UNIONALLSELECT1967,'0x0d950',55632UNIONALLSELECT1968,'0x05b57',23383UNIONALLSELECT1969,'0x056a0',22176UNIONALLSELECT1970,'0x096d0',38608UNIONALLSELECT1971,'0x04dd5',19925UNIONALLSELECT1972,'0x04ad0',19152UNIONALLSELECT1973,'0x0a4d0',42192UNIONALLSELECT1974,'0x0d4d4',54484UNIONALLSELECT1975,'0x0d250',53840UNIONALLSELECT1976,'0x0d558',54616UNIONALLSELECT1977,'0x0b540',46400UNIONALLSELECT1978,'0x0b5a0',46496UNIONALLSELECT1979,'0x195a6',103846UNIONALLSELECT1980,'0x095b0',38320UNIONALLSELECT1981,'0x049b0',18864UNIONALLSELECT1982,'0x0a974',43380UNIONALLSELECT1983,'0x0a4b0',42160UNIONALLSELECT1984,'0x0b27a',45690UNIONALLSELECT1985,'0x06a50',27216UNIONALLSELECT1986,'0x06d40',27968UNIONALLSELECT1987,'0x0af46',44870UNIONALLSELECT1988,'0x0ab60',43872UNIONALLSELECT1989,'0x09570',38256UNIONALLSELECT1990,'0x04af5',19189UNIONALLSELECT1991,'0x04970',18800UNIONALLSELECT1992,'0x064b0',25776UNIONALLSELECT1993,'0x074a3',29859UNIONALLSELECT1994,'0x0ea50',59984UNIONALLSELECT1995,'0x06b58',27480UNIONALLSELECT1996,'0x055c0',21952UNIONALLSELECT1997,'0x0ab60',43872UNIONALLSELECT1998,'0x096d5',38613UNIONALLSELECT1999,'0x092e0',37600UNIONALLSELECT2000,'0x0c960',51552UNIONALLSELECT2001,'0x0d954',55636UNIONALLSELECT2002,'0x0d4a0',54432UNIONALLSELECT2003,'0x0da50',55888UNIONALLSELECT2004,'0x07552',30034UNIONALLSELECT2005,'0x056a0',22176UNIONALLSELECT2006,'0x0abb7',43959UNIONALLSELECT2007,'0x025d0',9680UNIONALLSELECT2008,'0x092d0',37584UNIONALLSELECT2009，'0x0cab5'，51893UNIONALLSELECT2010，'0x0a950'，43344UNIONALLSELECT2011，'0x0b4a0'，46240UNIONALLSELECT2012,'0x0baa4',47780UNIONALLSELECT2013,'0x0ad50',44368UNIONALLSELECT2014,'0x055d9',21977UNIONALLSELECT2015,'0x04ba0',19360UNIONALLSELECT2016,'0x0a5b0',42416UNIONALLSELECT2017,'0x15176',86390UNIONALLSELECT2018,'0x052b0',21168UNIONALLSELECT2019,'0x0a930',43312UNIONALLSELECT2020,'0x07954',31060UNIONALLSELECT2021,'0x06aa0',27296UNIONALLSELECT2022,'0x0ad50',44368UNIONALLSELECT2023,'0x05b52',23378UNIONALLSELECT2024,'0x04b60',19296UNIONALLSELECT2025,'0x0a6e6',42726UNIONALLSELECT2026,'0x0a4e0',42208UNIONALLSELECT2027,'0x0d260',53856UNIONALLSELECT2028,'0x0ea65',60005UNIONALLSELECT2029,'0x0d530',54576UNIONALLSELECT2030,'0x05aa0',23200UNIONALLSELECT2031,'0x076a3',30371UNIONALLSELECT2032,'0x096d0',38608UNIONALLSELECT2033,'0x04bd7',19415UNIONALLSELECT2034,'0x04ad0',19152UNIONALLSELECT2035,'0x0a4d0',42192UNIONALLSELECT2036,'0x1d0b6',118966UNIONALLSELECT2037,'0x0d250',53840UNIONALLSELECT2038,'0x0d520',54560UNIONALLSELECT2039,'0x0dd45',56645UNIONALLSELECT2040,'0x0b5a0',46496UNIONALLSELECT2041,'0x056d0',22224UNIONALLSELECT2042,'0x055b2',21938UNIONALLSELECT2043,'0x049b0',18864UNIONALLSELECT2044,'0x0a577',42359UNIONALLSELECT2045,'0x0a4b0',42160UNIONALLSELECT2046,'0x0aa50',43600UNIONALLSELECT2047,'0x1b255',111189UNIONALLSELECT2048,'0x06d20',27936UNIONALLSELECT2049,'0x0ada0',44448（温馨提示：可以左右滑动代码）具体农历的计算有固定的规则。我们用下面的算法计算公历日期对应的农历注：以下算法来自网络createfunctiondbo.fn_GetLunar(@solardaydatetime)returnsnvarchar(30)asbegindeclare@soldataintdeclare@offsetintdeclare@ilunarintdeclare@iintdeclare@jintdeclare@ydaysintdeclare@mdaysintdeclare@mleapintdeclare@mleap1intdeclare@mleapnumintdeclare@bleapsmallintdeclare@tempintdeclare@yearnvarchar(10)declare@monthnvarchar(10)declare@daynvarchar(10)declare@chinesenumnvarchar(10)declare@outputdatenvarchar(30)set@offset=datediff(day,'1900-01-30',@solarday)--判断农历年的开始设置@i=1900--set@offset=@soldatawhile@i<2050and@offset>0beginset@ydays=348set@mleapnum=0select@ilunar=dataintfromsolardatawhereyearid=@i--返回农历年总天数set@j=32768while@j>8beginif@ilunar&@j>0set@ydays=@ydays+1set@j=@j/2end--返回农历1-12的闰月，如果没有闰月，则返回0set@mleap=@ilunar&15--返回农历年的闰月if@mleap>0beginif@ilunar&65536>0set@mleapnum=30elseset@mleapnum=29set@ydays=@ydays+@mleapnumendset@offset=@offset-@ydaysset@i=@i+1endif@offset<=0beginset@offset=@offset+@ydaysset@i=@i-1end--判断农历年末set@year=@i--判断农历月初set@i=1select@ilunar=dataintfromsolardatawhereyearid=@year--判断那个月是湿月set@mleap=@ilunar&15set@bleap=0while@i<13and@offset>0begin--判断运行月set@mdays=0if(@mleap>0and@i=(@mleap+1)and@bleap=0)begin--berunmonthset@i=@i-1set@bleap=1set@mleap1=@mleap--返回农历闰月的天数if@ilunar&65536>0set@mdays=30elseset@mdays=29endelse--不是运行月begin@j=1set@temp=65536while@j<=@ibeginset@temp=@temp/2set@j=@j+1endif@ilunar&@temp>0set@mdays=30elseset@mdays=29end--释放润月if@bleap=1and@i=(@mleap+1)set@bleap=0set@offset=@offset-@mdaysset@i=@i+1endif@offset<=0beginset@offset=@offset+@mdaysset@i=@i-1end--判断农历月末set@month=@i--判断农历月末set@day=ltrim(@offset)--输出日期set@chinesenum=N'〇1234567890'whilelen(@year)>0select@outputdate=isnull(@outputdate,'')+substring(@chinesenum,left(@year,1)+1,1),@year=stuff(@year,1,1,'')set@outputdate=@outputdate+N'年'+case@mleap1when@monththenN'run'else''endifcast(@monthasint)<10set@outputdate=@outputdate+case@monthwhen1thenN'positive'elsesubstring(@chinesenum,left(@month,1)+1,1)endelseifcast(@monthasint)>=10set@outputdate=@outputdate+case@monthwhen'10'thenN'ten'when11thenN'ten一'elseN'twelve'endset@outputdate=@outputdate+N'month'ifcast(@dayasint)<10set@outputdate=@outputdate+N'beginning'+substring(@chinesenum,left(@day,1)+1,1)elseif@daybetween'10'and'19'set@outputdate=@outputdate+case@daywhen'10'thenN'elseN'ten'+substring(@chinesenum,right(@day,1)+1,1)endelseif@daybetween'20'and'29'set@outputdate=@outputdate+case@daywhen'20'thenN'twenty'elseN'卷'end+case@daywhen'20'thenN''elsesubstring(@chinesenum,right(@day,1)+1,1)endelseset@outputdate=@outputdate+N'30'return@outputdateendGO测试农历算法我们创建的是一个农历函数，当我们将日期传递给这个函数时，它的具体日历将返回。SELECTdbo.fn_GetLunar('2021-02-19')Lunar检查返回的结果：我们检查了日历并验证了结果是否正确。将农历添加到日历表中，已经获得了公历转换为农历的转换函数，我们可以直接调用该函数。修改日历表结构（在CALENDAR_INFO表中增加一个字符串格式的字段Lunar）和存储过程。更新后的存储过程如下：CREATEPROCPROC_CALENDAR(@YEARINT)ASBEGINDECLARE@iINT;DECLARE@START_DATEVARCHAR(20);DECLARE@END_DATEVARCHAR(20);DECLARE@DATE_COUNTINT;SET@i=0;--定义一年的开始日期,使用CONCAT函数连接年月日期SET@START_DATE=CONCAT(@YEAR,'-01-01');--定义年的结束日期SET@END_DATE=CONCAT(@YEAR+1,'-01-01');--如果表中已经存在要创建的日历，则先删除DELETEFROMCALENDAR_INFOWHEREYEAR=@YEAR;--计算从开始日期到结束日期的总天数，DATEDIFF函数计算numberofdaysbetweendatesSET@DATE_COUNT=DATEDIFF(DAY,@START_DATE,@END_DATE);--创建一个循环，条件是@i小于一年中的天数WHILE@i<@DATE_COUNTBEGININSERTINTOCALENDAR_INFO(COUNTRY,CDR_CODE,DATE_NAME,LUNAR,YEAR,MONTH,WEEK,DAY,CDR_DATE,CREATOR,CREATE_DATE,CREATE_INST)SELECT'CN','CN01',--CONVERT函数将@START_DATE转换成指定日期edformatCONVERT(CHAR(10),@START_DATE,111)DATE_NAME,--将公历日期转换为农历dbo.fn_GetLunar(CONVERT(datetime,@start_date,101))LUNAR,--DATENAME获取年份@START_DATECONVERT(CHAR(4),DATENAME(YEAR,@START_DATE))YEAR,--DATENAME获取月份@START_DATECONVERT(CHAR(2),DATENAME(MONTH,@START_DATE))MONTH,--DATENAME获取月份@START_DATE的一周CONVERT(CHAR(6),DATENAME(WEEKDAY,@START_DATE))WEEK,--DATENAME获取@START_DATE的日期CONVERT(CHAR(2),DATENAME(DAY,@START_DATE))DAY,CONVERT(CHAR(10),@START_DATE,120)CDR_DATE,'平凡的世界',GETDATE()CREATE_DATE,'Lyven'--插入完成后，对@i加1SET@i=@i+1;--更新@START_DATE的值，使用DATEADD函数在SET@上加1原始基础START_DATE=CONVERT(CHAR(10),DATEADD(DAY,1,@START_DATE),120);ENDEND执行存储过程EXECproc_calendar2019，结果如下：我们检查了日历，验证结果是正确的。日期维表的功能可能有同学会问，我费了那么大劲写了这个，有什么用？既然叫维度表，那肯定和维度有关。有了这张维度表，我们就可以通过cube查看不同日期维度的具体数据。数据，尤其是可视化报表开发中的应用。下面是一个比较简单的PowerBI报表。这里我们使用日期维度表中的年份和月份。至此，一个完整的包含农历的日期维度表已经生成。有兴趣的小伙伴可以用MySQL或者Oracle改写。参考资料https://www.cnblogs.com/accumulator/articles/6807248.html
