万年历SQL Server中实现
参照C#版万年历实现而做
http://www.cnblogs.com/txw1958/archive/2013/01/27/csharp-calendar.html
在sql server中实现公历和农历的转换例子:
调用公历转农历
exec getLunarAndSolarDate 1995,10,2,8,2,0,1
solarDt solarYsolarM
solarDlunarDtStr
isLeapYisLeapM
curJQprevJQ
nextJQnGan
nZhiyGan
yZhirGan
rZhisGan
sZhiconsteName
animalchinaConstellation
SolarHolidayLunarHoliday
WeekDayHolidayWeek
1995-10-02 08:02:00.000 199510
2 农历一九九五年闰八月初八日
11
秋分 白露 寒露
乙亥
乙 酉 丙
寅壬
辰 天秤座 猪
房日兔国庆节假日
NULL国际住房日
星期一
调用农历转公历
exec getLunarAndSolarDate 1995,8,8,8,2,1,0
solarDt solarYsolarM
solarDlunarDtStr
isLeapYisLeapM
curJQprevJQ
nextJQnGan
nZhiyGan
yZhirGan
rZhisGan
sZhiconsteName
animalchinaConstellation
SolarHolidayLunarHoliday
WeekDayHolidayWeek
1995-10-02 00:00:00.000 199510
2 农历一九九五年闰八月初八日
11
秋分 白露 寒露
乙亥
乙 酉 丙
寅壬
辰 天秤座 猪
房日兔国庆节假日
NULL国际住房日
星期一
solarDt solarYsolarM solarDlunarDtStr isLeapYisLeapM curJQprevJQ nextJQnGan nZhiyGan yZhirGan rZhisGan sZhiconsteName animalchinaConstellation SolarHolidayLunarHoliday WeekDayHolidayWeek
1995-09-02 00:00:00.000 19959 2 农历一九九五年八月初八日 10 处暑 立秋 白露 乙亥 甲 申 丙 申壬 辰 狮子座 猪 亢金龙NULL NULLNULL 星期六
ALTER FUNCTION [dbo].[fBitShift] ( @i int = 1 ,-- integer @n int, -- shift @left bit -- if left shift ) RETURNS int AS BEGIN declare @m int,@s int if @left = 0 select @n%=32,@m=power(2,31-@n),@s=@i&@m,@i&=@m-1,@i*=power(2.,@n) else select @n%=32,@m=power(2,31-@n),@s=@i&@m,@i&=@m-1,@i/=power(2.,@n) if(@s>0)set @i|=0x80000000 return @i -- -1382285312 END
ALTER FUNCTION [dbo].[fConvertLunarDtStr]
(
@n int,
@type int -- 1:year 2:month 3:day
)
RETURNS nvarchar(10)
AS
BEGIN
declare @lStr nvarchar(10),@HZNum nvarchar(10),@nStr1 nvarchar(10),@nStr2 nvarchar(4),@nStr3 nvarchar(13)
set @HZNum = '零一二三四五六七八九'
set @nStr1 = N'日一二三四五六七八九'
set @nStr2 = N'初十廿卅'
if @type =1 and (@n <1 or @n >9) set @lStr=''
else if @type =2 and (@n <1 or @n >13) set @lStr=''
else if @type =3 and (@n <1 or @n >30) set @lStr=''
else if @type =3
begin
if @n = 10 set @lStr='初十'
else if @n = 20 set @lStr='二十'
else if @n = 30 set @lStr='三十'
else set @lStr=substring(@nStr2,@n/10+1,1)+substring(@nStr1,@n%10+1,1)
end
else
begin
if (@n <10) set @lStr=substring(@HZNum,@n+1,1)
if @type =2 and @n = 1 set @lStr='正'
if @n = 10 set @lStr='十'
if @n = 11 set @lStr='十一'
if @n = 12 set @lStr='腊'
end
return @lStrALTER FUNCTION [dbo].[fGetMonthDays]
(
@bitData int,
@month int,
@leap bit
)
RETURNS int
AS
BEGIN
declare @t1 int,@t2 int,@t3 binary(3),@t4 int
if @leap = 0
begin
set @t1 = @bitData & 0x0000FFFF
set @t2 = 16 - @month
set @t3 = dbo.fBitShift(1,@t2,0)
if @t1 & @t3 = 0
set @t4 = 29
else
set @t4 = 30
end
else
begin
if @bitData & 0x10000 = 0
set @t4 = 29
else
set @t4 = 30
end
return @t4
END
ALTER PROCEDURE [dbo].[zConvertLunarSolar]
@iyear int,
@imon int,
@iday int,
@ihour int,
@imin int,
@IsleapM bit,
@ToLunar bit
AS
BEGIN
SET NOCOUNT ON
begin try
--固定变量
DECLARE @msg NVARCHAR(MAX)=ERROR_MESSAGE()
begin
--返回值
declare @solarDt datetime,@leapdays int,@isLeapY bit,@solarY int,@solarM int,@solarD int,@lunarY int,@lunarM int,@lunarD int,@lunarDtStr nvarchar(50)
declare @curJQ nvarchar(4),@prevJQ nvarchar(4),@prevIQDt datetime,@nextJQ nvarchar(4),@nextJQDt datetime,@JieQiMonth int,@JQMonthFromDt datetime,@JQMonthToDt datetime --节气
declare @nGan nvarchar(1),@nZhi nvarchar(1),@yGan nvarchar(1),@yZhi nvarchar(1),@rGan nvarchar(1),@rZhi nvarchar(1),@sGan nvarchar(1),@sZhi nvarchar(1) --四柱
declare @consteName nvarchar(10),@animal nvarchar(2) --星座生肖
declare @chinaConstellation nvarchar(3) --28星宿
declare @SolarHoliday nvarchar(100),@LunarHoliday nvarchar(100),@WeekDayHoliday nvarchar(100),@Week nvarchar(3) --节日
--调用
--exec zConvertLunarSolar 1995,10,2,8,2,0,1
--exec zConvertLunarSolar 1995,8,8,8,2,0,0
--exec zConvertLunarSolar 1995,8,8,8,2,1,0
---------------------------------------
declare @MinYear int ,@MaxYear int
declare @startDt datetime, @gzStartYr datetime,@sartYr int,@chinaConste datetime,@chinaConsteStr nvarchar(200)
declare @ganStr nvarchar(10),@zhiStr nvarchar(12),@consteStr nvarchar(50), @animalStr nvarchar(12),@WeekStr nvarchar(100)
set @MinYear = 1900 --1900年为鼠年
set @MaxYear=2050
set @startDt = convert(datetime,'1900-01-30')
set @gzStartYr = convert(datetime,'1899-12-22')
set @sartYr = 1864 --干支计算起始年
set @chinaConste=convert(datetime,'2007-9-13')--28星宿参考值,本日为角
set @chinaConsteStr=N'角木蛟亢金龙女土蝠房日兔心月狐尾火虎箕水豹斗木獬牛金牛氐土貉虚日鼠危月燕室火猪壁水獝奎木狼娄金狗胃土彘昴日鸡毕月乌觜火猴参水猿井木犴鬼金羊柳土獐星日马张月鹿翼火蛇轸水蚓'
set @ganStr = N'甲乙丙丁戊己庚辛壬癸'
set @zhiStr = N'子丑寅卯辰巳午未申酉戌亥'
set @animalStr = N'鼠牛虎兔龙蛇马羊猴鸡狗猪'
set @WeekStr=N'星期日星期一星期二星期三星期四星期五星期六'
set @consteStr=N'白羊座金牛座双子座巨蟹座狮子座处女座天秤座天蝎座射手座摩羯座水瓶座双鱼座'
---------------------------------------
--varidate inpjut
if @ToLunar = 1 and ( @iyear<@MinYear or @iyear> @MaxYear-1 or @imon<1 or @imon>12 or @iday<1 or @iday>31)
RAISERROR ('非法公历日期', 16, 1)
if @ToLunar = 0 and ( @iyear<@MinYear or @iyear> @MaxYear or @imon<1 or @imon>12 or @iday<1 or @iday>30)
RAISERROR ('非法农历日期', 16, 1)
if @ihour<0 or @ihour>24 or @imin<0 or @imin>60
RAISERROR ('非法时间', 16, 1)
---------------------------------------
end
--创建农历年表用来保存年月天数,节气表,节日表
begin
create table #lunarYear( id int,bitdata binary(3))
INSERT #lunarYear (id,bitdata) VALUES (1, 0x004BD8)
INSERT #lunarYear (id,bitdata) VALUES (2, 0x004AE0)
INSERT #lunarYear (id,bitdata) VALUES (3, 0x00A570)
INSERT #lunarYear (id,bitdata) VALUES (4, 0x0054D5)
INSERT #lunarYear (id,bitdata) VALUES (5, 0x00D260)
INSERT #lunarYear (id,bitdata) VALUES (6, 0x00D950)
INSERT #lunarYear (id,bitdata) VALUES (7, 0x016554)
INSERT #lunarYear (id,bitdata) VALUES (8, 0x0056A0)
INSERT #lunarYear (id,bitdata) VALUES (9, 0x009AD0)
INSERT #lunarYear (id,bitdata) VALUES (10, 0x0055D2)
INSERT #lunarYear (id,bitdata) VALUES (11, 0x004AE0)
INSERT #lunarYear (id,bitdata) VALUES (12, 0x00A5B6)
INSERT #lunarYear (id,bitdata) VALUES (13, 0x00A4D0)
INSERT #lunarYear (id,bitdata) VALUES (14, 0x00D250)
INSERT #lunarYear (id,bitdata) VALUES (15, 0x01D255)
INSERT #lunarYear (id,bitdata) VALUES (16, 0x00B540)
INSERT #lunarYear (id,bitdata) VALUES (17, 0x00D6A0)
INSERT #lunarYear (id,bitdata) VALUES (18, 0x00ADA2)
INSERT #lunarYear (id,bitdata) VALUES (19, 0x0095B0)
INSERT #lunarYear (id,bitdata) VALUES (20, 0x014977)
INSERT #lunarYear (id,bitdata) VALUES (21, 0x004970)
INSERT #lunarYear (id,bitdata) VALUES (22, 0x00A4B0)
INSERT #lunarYear (id,bitdata) VALUES (23, 0x00B4B5)
INSERT #lunarYear (id,bitdata) VALUES (24, 0x006A50)
INSERT #lunarYear (id,bitdata) VALUES (25, 0x006D40)
INSERT #lunarYear (id,bitdata) VALUES (26, 0x01AB54)
INSERT #lunarYear (id,bitdata) VALUES (27, 0x002B60)
INSERT #lunarYear (id,bitdata) VALUES (28, 0x009570)
INSERT #lunarYear (id,bitdata) VALUES (29, 0x0052F2)
INSERT #lunarYear (id,bitdata) VALUES (30, 0x004970)
INSERT #lunarYear (id,bitdata) VALUES (31, 0x006566)
INSERT #lunarYear (id,bitdata) VALUES (32, 0x00D4A0)
INSERT #lunarYear (id,bitdata) VALUES (33, 0x00EA50)
INSERT #lunarYear (id,bitdata) VALUES (34, 0x006E95)
INSERT #lunarYear (id,bitdata) VALUES (35, 0x005AD0)
INSERT #lunarYear (id,bitdata) VALUES (36, 0x002B60)
INSERT #lunarYear (id,bitdata) VALUES (37, 0x0186E3)
INSERT #lunarYear (id,bitdata) VALUES (38, 0x0092E0)
INSERT #lunarYear (id,bitdata) VALUES (39, 0x01C8D7)
INSERT #lunarYear (id,bitdata) VALUES (40, 0x00C950)
INSERT #lunarYear (id,bitdata) VALUES (41, 0x00D4A0)
INSERT #lunarYear (id,bitdata) VALUES (42, 0x01D8A6)
INSERT #lunarYear (id,bitdata) VALUES (43, 0x00B550)
INSERT #lunarYear (id,bitdata) VALUES (44, 0x0056A0)
INSERT #lunarYear (id,bitdata) VALUES (45, 0x01A5B4)
INSERT #lunarYear (id,bitdata) VALUES (46, 0x0025D0)
INSERT #lunarYear (id,bitdata) VALUES (47, 0x0092D0)
INSERT #lunarYear (id,bitdata) VALUES (48, 0x00D2B2)
INSERT #lunarYear (id,bitdata) VALUES (49, 0x00A950)
INSERT #lunarYear (id,bitdata) VALUES (50, 0x00B557)
INSERT #lunarYear (id,bitdata) VALUES (51, 0x006CA0)
INSERT #lunarYear (id,bitdata) VALUES (52, 0x00B550)
INSERT #lunarYear (id,bitdata) VALUES (53, 0x015355)
INSERT #lunarYear (id,bitdata) VALUES (54, 0x004DA0)
INSERT #lunarYear (id,bitdata) VALUES (55, 0x00A5B0)
INSERT #lunarYear (id,bitdata) VALUES (56, 0x014573)
INSERT #lunarYear (id,bitdata) VALUES (57, 0x0052B0)
INSERT #lunarYear (id,bitdata) VALUES (58, 0x00A9A8)
INSERT #lunarYear (id,bitdata) VALUES (59, 0x00E950)
INSERT #lunarYear (id,bitdata) VALUES (60, 0x006AA0)
INSERT #lunarYear (id,bitdata) VALUES (61, 0x00AEA6)
INSERT #lunarYear (id,bitdata) VALUES (62, 0x00AB50)
INSERT #lunarYear (id,bitdata) VALUES (63, 0x004B60)
INSERT #lunarYear (id,bitdata) VALUES (64, 0x00AAE4)
INSERT #lunarYear (id,bitdata) VALUES (65, 0x00A570)
INSERT #lunarYear (id,bitdata) VALUES (66, 0x005260)
INSERT #lunarYear (id,bitdata) VALUES (67, 0x00F263)
INSERT #lunarYear (id,bitdata) VALUES (68, 0x00D950)
INSERT #lunarYear (id,bitdata) VALUES (69, 0x005B57)
INSERT #lunarYear (id,bitdata) VALUES (70, 0x0056A0)
INSERT #lunarYear (id,bitdata) VALUES (71, 0x0096D0)
INSERT #lunarYear (id,bitdata) VALUES (72, 0x004DD5)
INSERT #lunarYear (id,bitdata) VALUES (73, 0x004AD0)
INSERT #lunarYear (id,bitdata) VALUES (74, 0x00A4D0)
INSERT #lunarYear (id,bitdata) VALUES (75, 0x00D4D4)
INSERT #lunarYear (id,bitdata) VALUES (76, 0x00D250)
INSERT #lunarYear (id,bitdata) VALUES (77, 0x00D558)
INSERT #lunarYear (id,bitdata) VALUES (78, 0x00B540)
INSERT #lunarYear (id,bitdata) VALUES (79, 0x00B6A0)
INSERT #lunarYear (id,bitdata) VALUES (80, 0x0195A6)
INSERT #lunarYear (id,bitdata) VALUES (81, 0x0095B0)
INSERT #lunarYear (id,bitdata) VALUES (82, 0x0049B0)
INSERT #lunarYear (id,bitdata) VALUES (83, 0x00A974)
INSERT #lunarYear (id,bitdata) VALUES (84, 0x00A4B0)
INSERT #lunarYear (id,bitdata) VALUES (85, 0x00B27A)
INSERT #lunarYear (id,bitdata) VALUES (86, 0x006A50)
INSERT #lunarYear (id,bitdata) VALUES (87, 0x006D40)
INSERT #lunarYear (id,bitdata) VALUES (88, 0x00AF46)
INSERT #lunarYear (id,bitdata) VALUES (89, 0x00AB60)
INSERT #lunarYear (id,bitdata) VALUES (90, 0x009570)
INSERT #lunarYear (id,bitdata) VALUES (91, 0x004AF5)
INSERT #lunarYear (id,bitdata) VALUES (92, 0x004970)
INSERT #lunarYear (id,bitdata) VALUES (93, 0x0064B0)
INSERT #lunarYear (id,bitdata) VALUES (94, 0x0074A3)
INSERT #lunarYear (id,bitdata) VALUES (95, 0x00EA50)
INSERT #lunarYear (id,bitdata) VALUES (96, 0x006B58)
INSERT #lunarYear (id,bitdata) VALUES (97, 0x0055C0)
INSERT #lunarYear (id,bitdata) VALUES (98, 0x00AB60)
INSERT #lunarYear (id,bitdata) VALUES (99, 0x0096D5)
INSERT #lunarYear (id,bitdata) VALUES (100, 0x0092E0)
INSERT #lunarYear (id,bitdata) VALUES (101, 0x00C960)
INSERT #lunarYear (id,bitdata) VALUES (102, 0x00D954)
INSERT #lunarYear (id,bitdata) VALUES (103, 0x00D4A0)
INSERT #lunarYear (id,bitdata) VALUES (104, 0x00DA50)
INSERT #lunarYear (id,bitdata) VALUES (105, 0x007552)
INSERT #lunarYear (id,bitdata) VALUES (106, 0x0056A0)
INSERT #lunarYear (id,bitdata) VALUES (107, 0x00ABB7)
INSERT #lunarYear (id,bitdata) VALUES (108, 0x0025D0)
INSERT #lunarYear (id,bitdata) VALUES (109, 0x0092D0)
INSERT #lunarYear (id,bitdata) VALUES (110, 0x00CAB5)
INSERT #lunarYear (id,bitdata) VALUES (111, 0x00A950)
INSERT #lunarYear (id,bitdata) VALUES (112, 0x00B4A0)
INSERT #lunarYear (id,bitdata) VALUES (113, 0x00BAA4)
INSERT #lunarYear (id,bitdata) VALUES (114, 0x00AD50)
INSERT #lunarYear (id,bitdata) VALUES (115, 0x0055D9)
INSERT #lunarYear (id,bitdata) VALUES (116, 0x004BA0)
INSERT #lunarYear (id,bitdata) VALUES (117, 0x00A5B0)
INSERT #lunarYear (id,bitdata) VALUES (118, 0x015176)
INSERT #lunarYear (id,bitdata) VALUES (119, 0x0052B0)
INSERT #lunarYear (id,bitdata) VALUES (120, 0x00A930)
INSERT #lunarYear (id,bitdata) VALUES (121, 0x007954)
INSERT #lunarYear (id,bitdata) VALUES (122, 0x006AA0)
INSERT #lunarYear (id,bitdata) VALUES (123, 0x00AD50)
INSERT #lunarYear (id,bitdata) VALUES (124, 0x005B52)
INSERT #lunarYear (id,bitdata) VALUES (125, 0x004B60)
INSERT #lunarYear (id,bitdata) VALUES (126, 0x00A6E6)
INSERT #lunarYear (id,bitdata) VALUES (127, 0x00A4E0)
INSERT #lunarYear (id,bitdata) VALUES (128, 0x00D260)
INSERT #lunarYear (id,bitdata) VALUES (129, 0x00EA65)
INSERT #lunarYear (id,bitdata) VALUES (130, 0x00D530)
INSERT #lunarYear (id,bitdata) VALUES (131, 0x005AA0)
INSERT #lunarYear (id,bitdata) VALUES (132, 0x0076A3)
INSERT #lunarYear (id,bitdata) VALUES (133, 0x0096D0)
INSERT #lunarYear (id,bitdata) VALUES (134, 0x004BD7)
INSERT #lunarYear (id,bitdata) VALUES (135, 0x004AD0)
INSERT #lunarYear (id,bitdata) VALUES (136, 0x00A4D0)
INSERT #lunarYear (id,bitdata) VALUES (137, 0x01D0B6)
INSERT #lunarYear (id,bitdata) VALUES (138, 0x00D250)
INSERT #lunarYear (id,bitdata) VALUES (139, 0x00D520)
INSERT #lunarYear (id,bitdata) VALUES (140, 0x00DD45)
INSERT #lunarYear (id,bitdata) VALUES (141, 0x00B5A0)
INSERT #lunarYear (id,bitdata) VALUES (142, 0x0056D0)
INSERT #lunarYear (id,bitdata) VALUES (143, 0x0055B2)
INSERT #lunarYear (id,bitdata) VALUES (144, 0x0049B0)
INSERT #lunarYear (id,bitdata) VALUES (145, 0x00A577)
INSERT #lunarYear (id,bitdata) VALUES (146, 0x00A4B0)
INSERT #lunarYear (id,bitdata) VALUES (147, 0x00AA50)
INSERT #lunarYear (id,bitdata) VALUES (148, 0x01B255)
INSERT #lunarYear (id,bitdata) VALUES (149, 0x006D20)
INSERT #lunarYear (id,bitdata) VALUES (150, 0x00ADA0)
INSERT #lunarYear (id,bitdata) VALUES (151, 0x014B63)
create table #year(
yearno int,
bitdt binary(3),
bitdata int,
leapmon int,
ydays int,
fromdays int,
todays int
)
create table #ymday(
yearno int,
monno int,
mdays int,
leapdays int
)
CREATE TABLE #JieQi(
[JieQiId] [int] NOT NULL,
[JieQiMonth] [int] NOT NULL,
[JieQi] [varchar](50) NOT NULL,
[ZhiId] [int] NOT NULL,
[Minutes] [int] NOT NULL,
fromDt Datetime)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (1, 12, N'小寒', 2, 0)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (2, 12, N'大寒', 2, 21208)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (3, 1, N'立春', 3, 42467)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (4, 1, N'雨水', 3, 63836)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (5, 2, N'惊蛰', 4, 85337)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (6, 2, N'春分', 4, 107014)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (7, 3, N'清明', 5, 128867)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (8, 3, N'谷雨', 5, 150921)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (9, 4, N'立夏', 6, 173149)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (10, 4, N'小满', 6, 195551)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (11, 5, N'芒种', 7, 218072)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (12, 5, N'夏至', 7, 240693)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (13, 6, N'小暑', 8, 263343)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (14, 6, N'大暑', 8, 285989)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (15, 7, N'立秋', 9, 308563)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (16, 7, N'处暑', 9, 331033)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (17, 8, N'白露', 10, 353350)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (18, 8, N'秋分', 10, 375494)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (19, 9, N'寒露', 11, 397447)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (20, 9, N'霜降', 11, 419210)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (21, 10, N'立冬', 12, 440795)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (22, 10, N'小雪', 12, 462224)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (23, 11, N'大雪', 1, 483532)
INSERT #JieQi ([JieQiId], [JieQiMonth], [JieQi], [ZhiId], [Minutes]) VALUES (24, 11, N'冬至', 1, 504758)
create table #JieRi(
jieriid int,
jrtype int, -- 1:公历节日 2:农历节日 3:按第几个星期算的节日
hmon int,
hday int,
recess int,
holiday nvarchar(50)
)
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (1, 1, 1, 1, 1, N'元旦')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (2, 1, 2, 2, 0, N'世界湿地日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (3, 1, 2, 10, 0, N'国际气象节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (4, 1, 2, 14, 0, N'情人节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (5, 1, 3, 1, 0, N'国际海豹日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (6, 1, 3, 5, 0, N'学雷锋纪念日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (7, 1, 3, 8, 0, N'妇女节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (8, 1, 3, 12, 0, N'植树节 孙中山逝世纪念日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (9, 1, 3, 14, 0, N'国际警察日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (10, 1, 3, 15, 0, N'消费者权益日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (11, 1, 3, 17, 0, N'中国国医节 国际航海日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (12, 1, 3, 21, 0, N'世界森林日 消除种族歧视国际日 世界儿歌日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (13, 1, 3, 22, 0, N'世界水日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (14, 1, 3, 24, 0, N'世界防治结核病日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (15, 1, 4, 1, 0, N'愚人节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (16, 1, 4, 7, 0, N'世界卫生日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (17, 1, 4, 22, 0, N'世界地球日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (18, 1, 5, 1, 1, N'劳动节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (19, 1, 5, 2, 1, N'劳动节假日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (20, 1, 5, 3, 1, N'劳动节假日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (21, 1, 5, 4, 0, N'青年节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (22, 1, 5, 8, 0, N'世界红十字日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (23, 1, 5, 12, 0, N'国际护士节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (24, 1, 5, 31, 0, N'世界无烟日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (25, 1, 6, 1, 0, N'国际儿童节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (26, 1, 6, 5, 0, N'世界环境保护日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (27, 1, 6, 26, 0, N'国际禁毒日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (28, 1, 7, 1, 0, N'建党节 香港回归纪念 世界建筑日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (29, 1, 7, 11, 0, N'世界人口日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (30, 1, 8, 1, 0, N'建军节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (31, 1, 8, 8, 0, N'中国男子节 父亲节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (32, 1, 8, 15, 0, N'抗日战争胜利纪念')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (33, 1, 9, 9, 0, N' 逝世纪念')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (34, 1, 9, 10, 0, N'教师节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (35, 1, 9, 18, 0, N'九·一八事变纪念日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (36, 1, 9, 20, 0, N'国际爱牙日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (37, 1, 9, 27, 0, N'世界旅游日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (38, 1, 9, 28, 0, N'孔子诞辰')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (39, 1, 10, 1, 1, N'国庆节 国际音乐日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (40, 1, 10, 2, 1, N'国庆节假日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (41, 1, 10, 3, 1, N'国庆节假日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (42, 1, 10, 6, 0, N'老人节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (43, 1, 10, 24, 0, N'联合国日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (44, 1, 11, 10, 0, N'世界青年节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (45, 1, 11, 12, 0, N'孙中山诞辰纪念')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (46, 1, 12, 1, 0, N'世界艾滋病日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (47, 1, 12, 3, 0, N'世界残疾人日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (48, 1, 12, 20, 0, N'澳门回归纪念')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (49, 1, 12, 24, 0, N'平安夜')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (50, 1, 12, 25, 0, N'圣诞节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (51, 1, 12, 26, 0, N' 诞辰纪念')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (52, 2, 1, 1, 1, N'春节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (53, 2, 1, 15, 0, N'元宵节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (54, 2, 5, 5, 0, N'端午节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (55, 2, 7, 7, 0, N'七夕情人节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (56, 2, 7, 15, 0, N'中元节 盂兰盆节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (57, 2, 8, 15, 0, N'中秋节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (58, 2, 9, 9, 0, N'重阳节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (59, 2, 12, 8, 0, N'腊八节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (60, 2, 12, 23, 0, N'北方小年(扫房)')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (61, 2, 12, 24, 0, N'南方小年(掸尘)')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (62, 3, 5, 2, 1, N'母亲节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (63, 3, 5, 3, 1, N'全国助残日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (64, 3, 6, 3, 1, N'父亲节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (65, 3, 9, 3, 3, N'国际和平日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (66, 3, 9, 4, 1, N'国际聋人节')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (67, 3, 10, 1, 2, N'国际住房日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (68, 3, 10, 1, 4, N'国际减轻自然灾害日')
INSERT #JieRi ([jieriid], [jrtype], [hmon], [hday], [recess], [holiday]) VALUES (69, 3, 11, 4, 5, N'感恩节')
end
--生成年月天数
begin
insert into #year(yearNo,bitdt,bitData)
select id+1899,bitdata,bitdata from #lunarYear
update #year set leapmon=bitData & 0xF
insert into #ymday(yearno,monno)
select yearno,1 from #year
union
select yearno,2 from #year
union
select yearno,3 from #year
union
select yearno,4 from #year
union
select yearno,5 from #year
union
select yearno,6 from #year
union
select yearno,7 from #year
union
select yearno,8 from #year
union
select yearno,9 from #year
union
select yearno,10 from #year
union
select yearno,11 from #year
union
select yearno,12 from #year
update #ymday set mdays = dbo.fGetMonthDays(y.bitdata,#ymday.monno,0) from #year y where y.yearNo = #ymday.yearno
update #ymday set leapdays = dbo.fGetMonthDays(y.bitdata,y.leapmon,1) from #year y where y.yearNo = #ymday.yearno and y.leapmon=#ymday.monno
update #year set ydays=(select sum(ym.mdays)+sum(isnull(ym.leapdays,0)) from #ymday ym where ym.yearno=#year.yearno)
update #year set fromdays=(select sum(y1.ydays) from #year y1 where y1.yearno<#year.yearno),todays=(select sum(y2.ydays) from #year y2 where y2.yearno<#year.yearno+1)
end
--select * from #year
--农历阴历转换
declare @dayDiff int,@fromdays int,@mdays int
declare @lunarYStr nvarchar(10),@lunarMStr nvarchar(10),@lunarDStr nvarchar(10)
begin
if @ToLunar = 1
begin
-- 公历转换成阴历
set @solarY = @iyear
set @solarM=@imon
set @solarD=@iday
set @solarDt = convert(datetime,convert(varchar(4),@solarY)+'-'+convert(varchar(2),@solarM)+'-'+convert(varchar(2),@solarD)+' '+convert(varchar(2),@ihour)+':'+convert(varchar(2),@imin)+':00',20)
if @solarDt<'1900-01-30' or @solarDt>'2049-12-31' RAISERROR ('超出可转换的日期', 16, 1)
set @dayDiff = datediff(d,@startDt,@solarDt)
select @lunarY=y.yearno,@isLeapY =(case leapmon when 0 then 0 else 1 end),@fromdays=fromdays from #year y where @dayDiff between fromdays and todays
set @dayDiff = @dayDiff - @fromdays
select @lunarM=ym.monno,@dayDiff=@dayDiff-(select sum(ym1.mdays)+sum(isnull(ym1.leapdays,0)) from #ymday ym1 where ym1.yearno= ym.yearno and ym1.monno<ym.monno)
,@mdays=mdays,@leapdays=leapdays
from #ymday ym where ym.yearno=@lunarY and @dayDiff between (select sum(ym1.mdays)+sum(isnull(ym1.leapdays,0)) from #ymday ym1 where ym1.yearno= ym.yearno and ym1.monno<ym.monno) and
(select sum(ym2.mdays)+sum(isnull(ym2.leapdays,0)) from #ymday ym2 where ym2.yearno= ym.yearno and ym2.monno<ym.monno+1)
if @dayDiff>@mdays
begin
set @lunarD = @dayDiff - @mdays
set @isLeapM = 1
end
else
begin
set @lunarD = @dayDiff
set @isLeapM = 0
end
end
else
begin
-- 阴历转换成公历
set @lunarY = @iyear
set @lunarM=@imon
set @lunarD=@iday
declare @leapmon int
select @dayDiff = fromdays,@isLeapY =(case leapmon when 0 then 0 else 1 end),@leapmon=leapmon from #year where yearno=@lunarY
if @IsleapM = 1 and @lunarM <> @leapmon
begin
set @IsleapM = 0
--RAISERROR ('非法农历日期', 16, 1)
end
select @dayDiff=@dayDiff+(select sum(ym1.mdays)+sum(isnull(ym1.leapdays,0)) from #ymday ym1 where ym1.yearno=ym.yearno and ym1.monno<ym.monno)+(case @IsleapM when 1 then mdays else 0 end)+@lunarD
from #ymday ym where ym.yearno=@lunarY and ym.monno=@lunarM
set @solarDt = dateadd(day,@dayDiff,@startDt)
set @solarY = datepart(year,@solarDt)
set @solarM=datepart(month,@solarDt)
set @solarD=datepart(day,@solarDt)
end
set @lunarYStr = dbo.fConvertLunarDtStr(@lunarY/1000,1)+dbo.fConvertLunarDtStr((@lunarY%1000)/100,1)+dbo.fConvertLunarDtStr((@lunarY%100)/10,1)+dbo.fConvertLunarDtStr(@lunarY%10,1)
set @lunarMStr = dbo.fConvertLunarDtStr(@lunarM,2)
set @lunarDStr = dbo.fConvertLunarDtStr(@lunarD,3)
set @lunarDtStr= '农历' + @lunarYStr + '年'+ (case @isLeapM when 1 then '闰' else '' end)+ @lunarMStr +'月'+ @lunarDStr+'日'
end
--四柱干支
begin
declare @tmpGan nvarchar(12),@indexGan int , @i int,@tHour int,@tMin int,@offset int
--年干支
set @i=(@lunarY-@sartYr)%60
set @nGan = substring(@ganStr,@i%10+1,1)
set @nZhi = substring(@zhiStr,@i%12+1,1)
--月干支
declare @jieQiStartDt datetime,@JieQiId int
set @jieQiStartDt = convert(datetime,'1900-01-06 02:05:00',20)
update #JieQi set fromDt = dateadd(minute,525948.76 * (@solarY - 1900) + Minutes,@jieQiStartDt)
select @curJQ=JieQi,@JieQiId=JieQiId,@JieQiMonth=JieQiMonth,@yZhi=substring(@zhiStr,ZhiId,1),@prevIQDt = fromDt
from #JieQi jq where @solarDt between fromDt and
(select fromDt from #JieQi jq2 where jq2.JieQiId = jq.JieQiId+1)
select @prevJQ=JieQi from #JieQi jq where jq.JieQiId=(case @JieQiId when 1 then 24 else @JieQiId-1 end)
select @nextJQ=JieQi,@nextJQDt = fromDt from #JieQi jq where jq.JieQiId=(case @JieQiId when 24 then 1 else @JieQiId+1 end)
select @JQMonthFromDt=fromDt from #JieQi jq where JieQiMonth = @JieQiMonth and JieQiId%2=1
select @JQMonthToDt=fromDt from #JieQi jq where JieQiMonth = @JieQiMonth+1 and JieQiId%2=1
--按照节气定月干支
set @i = @i%10
select @yGan =substring(@ganStr,((case @i when 0 then 3 when 1 then 5 when 2 then 7 when 3 then 9 when 4 then 1
when 5 then 3 when 6 then 5 when 7 then 7 when 8 then 9 when 9 then 1 end)+@JieQiMonth-2)%10+1,1)
--日干支
set @dayDiff = datediff(d,@gzStartYr,@solarDt)
set @i = @dayDiff%60
set @rGan = substring(@ganStr,@i%10+1,1)
set @rZhi = substring(@zhiStr,@i%12+1,1)
--时干支
set @tHour = @ihour
set @tMin = @imin
set @i = @i%10
if @imin != 0 set @tHour += 1
set @offset = @tHour/2
if @offset >=12 set @offset=0
select @sGan =substring(@ganStr,((case @i when 0 then 1 when 1 then 3 when 2 then 5 when 3 then 7 when 4 then 9
when 5 then 1 when 6 then 3 when 7 then 5 when 8 then 7 when 9 then 9 end)+@offset-1)%10+1,1)
--set @indexGan = ((@i % 10 + 1) * 2 -1) % 10 ; --ganStr[i % 10] 为日的天干,(n*2-1) %10得出地支对应,n从1开始
--set @tmpGan = substring(@ganStr,@indexGan,10-@indexGan)+substring(@ganStr,0,@indexGan+2) -- 凑齐12位
--set @sGan = substring(@tmpGan,@offset+1,1)
set @sZhi = substring(@zhiStr,@offset+1,1)
end
--星座
set @i=@solarM *100 + @solarD
if (((@i >= 321) and (@i <= 419))) set @offset=0
else if ((@i >= 420) and (@i <= 520)) set @offset=1
else if ((@i >= 521) and (@i <= 620)) set @offset=2
else if ((@i >= 621) and (@i <= 722)) set @offset=3
else if ((@i= 823) and (@i <= 922)) set @offset=4
else if ((@i= 823) and (@i <= 922)) set @offset=5
else if ((@i >= 923) and (@i <= 1022)) set @offset=6
else if ((@i >= 1023) and (@i <= 1121)) set @offset=7
else if ((@i >= 1122) and (@i <= 1221)) set @offset=8
else if ((@i >= 1222) or (@i <= 119)) set @offset=9
else if ((@i >= 120) and (@i <= 218)) set @offset=10
else if ((@i >= 219) and (@i <= 320)) set @offset=11
set @consteName= substring(@consteStr,@offset*3+1,3)
--属相
set @animal = substring(@animalStr,(@solarY-@MinYear)%12+1,1)
--28星宿计算
set @i = datediff(d,@chinaConste,@solarDt)%28
if @i >= 0 set @chinaConstellation = substring(@chinaConsteStr,@i*3+1,3)
else set @chinaConstellation = substring(@chinaConsteStr,(27+@i)*3+1,3)
--节日
declare @wOfMon int,@firstMonthDay datetime
select @SolarHoliday=holiday from #JieRi where jrtype=1 and hmon=@SolarM and hday=@SolarD
if @IsLeapM = 0
select @SolarHoliday=holiday from #JieRi where jrtype=2 and hmon=@LunarM and hday=@LunarD
if @LunarM = 12 --除夕
begin
declare @Bitdata int,@WeekOfMonth int,@dayOfWeek int
select @Bitdata=bitdata from #year where yearno=@LunarY
set @i=dbo.fGetMonthDays(@Bitdata,12,0)
if @LunarD = @i set @SolarHoliday=N'除夕'
end
set @dayOfWeek = datepart(dw, @SolarDt)
set @Week = substring(@WeekStr,(@dayOfWeek-1)*3+1,3)
set @firstMonthDay = dateadd(day,1-@SolarD,@SolarDt)
set @WeekOfMonth = datepart(week,@SolarDt)-datepart(week,dateadd(day,1-@SolarD,@SolarDt))+1
set @i = datepart(dw, @firstMonthDay)
select @WeekDayHoliday=holiday from #JieRi where jrtype=3 and hmon=@SolarM and recess=@dayOfWeek
and ((@i>=@dayOfWeek and hday = @WeekOfMonth-1) or (@i<@dayOfWeek and hday = @WeekOfMonth))
----------------------
select @solarDt as solarDt,@solarY as solarY,@solarM as solarM,@solarD as solarD
,@lunarDtStr as lunarDtStr,@lunarY as lunarY,@lunarM as lunarM,@lunarD as lunarD,@isLeapY as isLeapY,@isLeapM as isLeapM
,@curJQ as curJQ,@prevJQ as prevJQ ,@prevIQDt as prevIQDt ,@nextJQ as nextJQ,@nextJQDt as nextJQDt,@JQMonthFromDt as JQMonthFromDt ,@JQMonthToDt as JQMonthToDt
,@nGan as nGan,@nZhi as nZhi,@yGan as yGan,@yZhi as yZhi,@rGan as rGan,@rZhi as rZhi,@sGan as sGan,@sZhi as sZhi --四柱
,@consteName as consteName,@animal as animal,@chinaConstellation as chinaConstellation --28星宿
,@SolarHoliday as SolarHoliday,@LunarHoliday as LunarHoliday,@WeekDayHoliday as WeekDayHoliday,@Week as Week--节日
drop table #lunarYear
drop table #year
drop table #ymday
drop table #JieQi
drop table #JieRi
end try
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
END
