生日提醒之农历，公历问题

1.创建基础数据--1900至2049年公历，农历比照表

代码

1 CREATE TABLE SolarData
2 (
3 yearId int not null ,
4 data char ( 7 ) not null ,
5 dataInt int not null
6 )
7   GO
8   -- 插入数据
9   INSERT INTO
10 SolarData SELECT 1900 , ' 0x04bd8 ' , 19416 UNION ALL SELECT 1901 , ' 0x04ae0 ' , 19168
11   UNION ALL SELECT 1902 , ' 0x0a570 ' , 42352 UNION ALL SELECT 1903 , ' 0x054d5 ' , 21717
12   UNION ALL SELECT 1904 , ' 0x0d260 ' , 53856 UNION ALL SELECT 1905 , ' 0x0d950 ' , 55632
13 UNION ALL SELECT 1906 , ' 0x16554 ' , 91476 UNION ALL SELECT 1907 , ' 0x056a0 ' , 22176
14 UNION ALL SELECT 1908 , ' 0x09ad0 ' , 39632 UNION ALL SELECT 1909 , ' 0x055d2 ' , 21970
15 UNION ALL SELECT 1910 , ' 0x04ae0 ' , 19168 UNION ALL SELECT 1911 , ' 0x0a5b6 ' , 42422
16 UNION ALL SELECT 1912 , ' 0x0a4d0 ' , 42192 UNION ALL SELECT 1913 , ' 0x0d250 ' , 53840
17 UNION ALL SELECT 1914 , ' 0x1d255 ' , 119381 UNION ALL SELECT 1915 , ' 0x0b540 ' , 46400
18 UNION ALL SELECT 1916 , ' 0x0d6a0 ' , 54944 UNION ALL SELECT 1917 , ' 0x0ada2 ' , 44450
19 UNION ALL SELECT 1918 , ' 0x095b0 ' , 38320 UNION ALL SELECT 1919 , ' 0x14977 ' , 84343
20 UNION ALL SELECT 1920 , ' 0x04970 ' , 18800 UNION ALL SELECT 1921 , ' 0x0a4b0 ' , 42160
21 UNION ALL SELECT 1922 , ' 0x0b4b5 ' , 46261 UNION ALL SELECT 1923 , ' 0x06a50 ' , 27216
22 UNION ALL SELECT 1924 , ' 0x06d40 ' , 27968 UNION ALL SELECT 1925 , ' 0x1ab54 ' , 109396
23 UNION ALL SELECT 1926 , ' 0x02b60 ' , 11104 UNION ALL SELECT 1927 , ' 0x09570 ' , 38256
24 UNION ALL SELECT 1928 , ' 0x052f2 ' , 21234 UNION ALL SELECT 1929 , ' 0x04970 ' , 18800
25 UNION ALL SELECT 1930 , ' 0x06566 ' , 25958 UNION ALL SELECT 1931 , ' 0x0d4a0 ' , 54432
26 UNION ALL SELECT 1932 , ' 0x0ea50 ' , 59984 UNION ALL SELECT 1933 , ' 0x06e95 ' , 28309
27 UNION ALL SELECT 1934 , ' 0x05ad0 ' , 23248 UNION ALL SELECT 1935 , ' 0x02b60 ' , 11104
28 UNION ALL SELECT 1936 , ' 0x186e3 ' , 100067 UNION ALL SELECT 1937 , ' 0x092e0 ' , 37600
29 UNION ALL SELECT 1938 , ' 0x1c8d7 ' , 116951 UNION ALL SELECT 1939 , ' 0x0c950 ' , 51536
30 UNION ALL SELECT 1940 , ' 0x0d4a0 ' , 54432 UNION ALL SELECT 1941 , ' 0x1d8a6 ' , 120998
31 UNION ALL SELECT 1942 , ' 0x0b550 ' , 46416 UNION ALL SELECT 1943 , ' 0x056a0 ' , 22176
32 UNION ALL SELECT 1944 , ' 0x1a5b4 ' , 107956 UNION ALL SELECT 1945 , ' 0x025d0 ' , 9680
33 UNION ALL SELECT 1946 , ' 0x092d0 ' , 37584 UNION ALL SELECT 1947 , ' 0x0d2b2 ' , 53938
34 UNION ALL SELECT 1948 , ' 0x0a950 ' , 43344 UNION ALL SELECT 1949 , ' 0x0b557 ' , 46423
35 UNION ALL SELECT 1950 , ' 0x06ca0 ' , 27808 UNION ALL SELECT 1951 , ' 0x0b550 ' , 46416
36 UNION ALL SELECT 1952 , ' 0x15355 ' , 86869 UNION ALL SELECT 1953 , ' 0x04da0 ' , 19872
37 UNION ALL SELECT 1954 , ' 0x0a5d0 ' , 42448 UNION ALL SELECT 1955 , ' 0x14573 ' , 83315
38 UNION ALL SELECT 1956 , ' 0x052d0 ' , 21200 UNION ALL SELECT 1957 , ' 0x0a9a8 ' , 43432
39 UNION ALL SELECT 1958 , ' 0x0e950 ' , 59728 UNION ALL SELECT 1959 , ' 0x06aa0 ' , 27296
40 UNION ALL SELECT 1960 , ' 0x0aea6 ' , 44710 UNION ALL SELECT 1961 , ' 0x0ab50 ' , 43856
41 UNION ALL SELECT 1962 , ' 0x04b60 ' , 19296 UNION ALL SELECT 1963 , ' 0x0aae4 ' , 43748
42 UNION ALL SELECT 1964 , ' 0x0a570 ' , 42352 UNION ALL SELECT 1965 , ' 0x05260 ' , 21088
43 UNION ALL SELECT 1966 , ' 0x0f263 ' , 62051 UNION ALL SELECT 1967 , ' 0x0d950 ' , 55632
44 UNION ALL SELECT 1968 , ' 0x05b57 ' , 23383 UNION ALL SELECT 1969 , ' 0x056a0 ' , 22176
45 UNION ALL SELECT 1970 , ' 0x096d0 ' , 38608 UNION ALL SELECT 1971 , ' 0x04dd5 ' , 19925
46 UNION ALL SELECT 1972 , ' 0x04ad0 ' , 19152 UNION ALL SELECT 1973 , ' 0x0a4d0 ' , 42192
47 UNION ALL SELECT 1974 , ' 0x0d4d4 ' , 54484 UNION ALL SELECT 1975 , ' 0x0d250 ' , 53840
48 UNION ALL SELECT 1976 , ' 0x0d558 ' , 54616 UNION ALL SELECT 1977 , ' 0x0b540 ' , 46400
49 UNION ALL SELECT 1978 , ' 0x0b5a0 ' , 46496 UNION ALL SELECT 1979 , ' 0x195a6 ' , 103846
50 UNION ALL SELECT 1980 , ' 0x095b0 ' , 38320 UNION ALL SELECT 1981 , ' 0x049b0 ' , 18864
51 UNION ALL SELECT 1982 , ' 0x0a974 ' , 43380 UNION ALL SELECT 1983 , ' 0x0a4b0 ' , 42160
52 UNION ALL SELECT 1984 , ' 0x0b27a ' , 45690 UNION ALL SELECT 1985 , ' 0x06a50 ' , 27216
53 UNION ALL SELECT 1986 , ' 0x06d40 ' , 27968 UNION ALL SELECT 1987 , ' 0x0af46 ' , 44870
54 UNION ALL SELECT 1988 , ' 0x0ab60 ' , 43872 UNION ALL SELECT 1989 , ' 0x09570 ' , 38256
55 UNION ALL SELECT 1990 , ' 0x04af5 ' , 19189 UNION ALL SELECT 1991 , ' 0x04970 ' , 18800
56 UNION ALL SELECT 1992 , ' 0x064b0 ' , 25776 UNION ALL SELECT 1993 , ' 0x074a3 ' , 29859
57 UNION ALL SELECT 1994 , ' 0x0ea50 ' , 59984 UNION ALL SELECT 1995 , ' 0x06b58 ' , 27480
58 UNION ALL SELECT 1996 , ' 0x055c0 ' , 21952 UNION ALL SELECT 1997 , ' 0x0ab60 ' , 43872
59 UNION ALL SELECT 1998 , ' 0x096d5 ' , 38613 UNION ALL SELECT 1999 , ' 0x092e0 ' , 37600
60 UNION ALL SELECT 2000 , ' 0x0c960 ' , 51552 UNION ALL SELECT 2001 , ' 0x0d954 ' , 55636
61 UNION ALL SELECT 2002 , ' 0x0d4a0 ' , 54432 UNION ALL SELECT 2003 , ' 0x0da50 ' , 55888
62 UNION ALL SELECT 2004 , ' 0x07552 ' , 30034 UNION ALL SELECT 2005 , ' 0x056a0 ' , 22176
63 UNION ALL SELECT 2006 , ' 0x0abb7 ' , 43959 UNION ALL SELECT 2007 , ' 0x025d0 ' , 9680
64 UNION ALL SELECT 2008 , ' 0x092d0 ' , 37584 UNION ALL SELECT 2009 , ' 0x0cab5 ' , 51893
65 UNION ALL SELECT 2010 , ' 0x0a950 ' , 43344 UNION ALL SELECT 2011 , ' 0x0b4a0 ' , 46240
66 UNION ALL SELECT 2012 , ' 0x0baa4 ' , 47780 UNION ALL SELECT 2013 , ' 0x0ad50 ' , 44368
67 UNION ALL SELECT 2014 , ' 0x055d9 ' , 21977 UNION ALL SELECT 2015 , ' 0x04ba0 ' , 19360
68 UNION ALL SELECT 2016 , ' 0x0a5b0 ' , 42416 UNION ALL SELECT 2017 , ' 0x15176 ' , 86390
69 UNION ALL SELECT 2018 , ' 0x052b0 ' , 21168 UNION ALL SELECT 2019 , ' 0x0a930 ' , 43312
70 UNION ALL SELECT 2020 , ' 0x07954 ' , 31060 UNION ALL SELECT 2021 , ' 0x06aa0 ' , 27296
71 UNION ALL SELECT 2022 , ' 0x0ad50 ' , 44368 UNION ALL SELECT 2023 , ' 0x05b52 ' , 23378
72 UNION ALL SELECT 2024 , ' 0x04b60 ' , 19296 UNION ALL SELECT 2025 , ' 0x0a6e6 ' , 42726
73 UNION ALL SELECT 2026 , ' 0x0a4e0 ' , 42208 UNION ALL SELECT 2027 , ' 0x0d260 ' , 53856
74 UNION ALL SELECT 2028 , ' 0x0ea65 ' , 60005 UNION ALL SELECT 2029 , ' 0x0d530 ' , 54576
75 UNION ALL SELECT 2030 , ' 0x05aa0 ' , 23200 UNION ALL SELECT 2031 , ' 0x076a3 ' , 30371
76 UNION ALL SELECT 2032 , ' 0x096d0 ' , 38608 UNION ALL SELECT 2033 , ' 0x04bd7 ' , 19415
77 UNION ALL SELECT 2034 , ' 0x04ad0 ' , 19152 UNION ALL SELECT 2035 , ' 0x0a4d0 ' , 42192
78 UNION ALL SELECT 2036 , ' 0x1d0b6 ' , 118966 UNION ALL SELECT 2037 , ' 0x0d250 ' , 53840
79 UNION ALL SELECT 2038 , ' 0x0d520 ' , 54560 UNION ALL SELECT 2039 , ' 0x0dd45 ' , 56645
80 UNION ALL SELECT 2040 , ' 0x0b5a0 ' , 46496 UNION ALL SELECT 2041 , ' 0x056d0 ' , 22224
81 UNION ALL SELECT 2042 , ' 0x055b2 ' , 21938 UNION ALL SELECT 2043 , ' 0x049b0 ' , 18864
82 UNION ALL SELECT 2044 , ' 0x0a577 ' , 42359 UNION ALL SELECT 2045 , ' 0x0a4b0 ' , 42160
83 UNION ALL SELECT 2046 , ' 0x0aa50 ' , 43600 UNION ALL SELECT 2047 , ' 0x1b255 ' , 111189
84 UNION ALL SELECT 2048 , ' 0x06d20 ' , 27936 UNION ALL SELECT 2049 , ' 0x0ada0 ' , 44448

--这个不知道怎么比照的，也没研究过。来源于网络：http://topic.csdn.net/u/20100424/07/29529D9D-BC54-4877-B198-4426B4D85024.html

2.获取农历的方法

公历获取农历

1 CREATE FUNCTION fn_GetLunar( @solarDay DATETIME )
2 RETURNS VARCHAR ( 10 )
3 AS
4 BEGIN
5 DECLARE @solData int
6 DECLARE @offset int
7 DECLARE @iLunar int
8 DECLARE @i INT
9 DECLARE @j INT
10 DECLARE @yDays int
11 DECLARE @mDays int
12 DECLARE @mLeap int
13 DECLARE @mLeapNum int
14 DECLARE @bLeap smallint
15 DECLARE @temp int
16
17 DECLARE @YEAR INT
18 DECLARE @MONTH INT
19 DECLARE @DAY INT
20
21 DECLARE @OUTPUTDATE VARCHAR ( 10 )
22
23 -- 保证传进来的日期是不带时间
24 SET @solarDay = CONVERT ( NVARCHAR ( 10 ), @solarDay , 120 ) -- cast(@solarDay AS char(10))
25 SET @offset = CAST ( @solarDay - ' 1900-01-30 ' AS INT )
26
27
28 -- 确定农历年开始
29 SET @i = 1900
30 -- SET @offset=@solData
31 WHILE @i < 2050 AND @offset > 0
32 BEGIN
33 SET @yDays = 348
34 SET @mLeapNum = 0
35 SELECT @iLunar = dataInt FROM SolarData WHERE yearId = @i
36
37 -- 传回农历年的总天数
38 SET @j = 32768
39 WHILE @j > 8
40 BEGIN
41 IF @iLunar & @j > 0
42 SET @yDays = @yDays + 1
43 SET @j = @j / 2
44 END
45
46 -- 传回农历年闰哪个月 1-12 , 没闰传回 0
47 SET @mLeap = @iLunar & 15
48
49 -- 传回农历年闰月的天数 ,加在年的总天数上
50 IF @mLeap > 0
51 BEGIN
52 IF @iLunar & 65536 > 0
53 SET @mLeapNum = 30
54 ELSE
55 SET @mLeapNum = 29
56
57 SET @yDays = @yDays + @mLeapNum
58 END
59
60 SET @offset = @offset - @yDays
61 SET @i = @i + 1
62 END
63
64 IF @offset <= 0
65 BEGIN
66 SET @offset = @offset + @yDays
67 SET @i = @i - 1
68 END
69 -- 确定农历年结束
70 SET @YEAR = @i
71
72 -- 确定农历月开始
73 SET @i = 1
74 SELECT @iLunar = dataInt FROM SolarData WHERE yearId = @YEAR
75
76 -- 判断那个月是润月
77 SET @mLeap = @iLunar & 15
78 SET @bLeap = 0
79
80 WHILE @i < 13 AND @offset > 0
81 BEGIN
82 -- 判断润月
83 SET @mDays = 0
84 IF ( @mLeap > 0 AND @i = ( @mLeap + 1 ) AND @bLeap = 0 )
85 BEGIN -- 是润月
86 SET @i = @i - 1
87 SET @bLeap = 1
88 -- 传回农历年闰月的天数
89 IF @iLunar & 65536 > 0
90 SET @mDays = 30
91 ELSE
92 SET @mDays = 29
93 END
94 ELSE
95 -- 不是润月
96 BEGIN
97 SET @j = 1
98 SET @temp = 65536
99 WHILE @j <= @i
100 BEGIN
101 SET @temp = @temp / 2
102 SET @j = @j + 1
103 END
104
105 IF @iLunar & @temp > 0
106 SET @mDays = 30
107 ELSE
108 SET @mDays = 29
109 END
110
111 -- 解除闰月
112 IF @bLeap = 1 AND @i = ( @mLeap + 1 )
113 SET @bLeap = 0
114
115 SET @offset = @offset - @mDays
116 SET @i = @i + 1
117 END
118
119 IF @offset <= 0
120 BEGIN
121 SET @offset = @offset + @mDays
122 SET @i = @i - 1
123 END
124
125 -- 确定农历月结束
126 SET @MONTH = @i
127
128 -- 确定农历日结束
129 SET @DAY = @offset
130
131 SET @OUTPUTDATE = CAST ( @YEAR AS VARCHAR ( 4 )) + ' - ' +RIGHT ( ' 0 ' + CAST ( @MONTH AS VARCHAR ( 2 )), 2 ) + ' - ' +RIGHT ( ' 0 ' + CAST ( @DAY AS VARCHAR ( 2 )), 2 ) -- CAST((CAST(@YEAR AS VARCHAR(4))+'-'+CAST(@MONTH AS VARCHAR(2))+'-'+CAST(@DAY AS VARCHAR(2))) AS DATETIME)
132 RETURN @OUTPUTDATE
133 END
134
135 -- 调用方法
136 -- DECLARE @d DATETIME;
137 -- SET @d=GETDATE();
138 -- SELECT dbo.fn_GetLunar(@d)
139 GO
140

--来源于网络：http://topic.csdn.net/u/20100424/07/29529D9D-BC54-4877-B198-4426B4D85024.html

--并改进了有问题部分

3.创建公历，农历详细比照表

公历农历详细比照表

1 CREATE TABLE [ dbo ] . [ SolarLunarData ] (
2 [ RecID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
3 [ SolarDate ] [ datetime ] NOT NULL ,
4 [ LunarDate ] [ varchar ] ( 10 ) NOT NULL ,
5 CONSTRAINT [ PK_SolarLunarData ] PRIMARY KEY CLUSTERED
6 (
7 [ SolarDate ] ASC
8 ) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
9 ) ON [ PRIMARY ]
10
11 GO

4.生成数据于公历农历详细比照表

导入数据至公历农历比照表

1 TRUNCATE TABLE SolarLunarData
2 GO
3 DECLARE @i INT
4 SET @i = 0
5 WHILE @i < 3000
6 BEGIN
7 INSERT INTO SolarLunarData(SolarDate,LunarDate)
8 SELECT CONVERT ( CHAR ( 10 ), DATEADD (d, @i , ' 2010-01-01 ' ), 23 ),
9 dbo.fn_GetLunar( DATEADD (d, @i , ' 2010-01-01 ' ))
10 SET @i = @i + 1
11 END

 

 5.创建农历获取公历函数

农历获取公历日期函数

1 IF object_id ( ' fn_GetSolar ' ) IS NOT NULL
2 DROP FUNCTION fn_GetSolar
3   GO
4   CREATE FUNCTION dbo.fn_GetSolar( @day VARCHAR ( 10 ))
5 RETURNS DATETIME
6 AS
7 BEGIN
8 RETURN ( SELECT SolarDate FROM SolarLunarData WHERE LunarDate = @day )
9 END
10 GO

 

 

 

 

 

转载于:https://www.cnblogs.com/s021368/articles/1853371.html