计算农历日期函数
最近在网上看到了一个用PLSQL直接从公历来计算农历的过程,发觉很好。一方面可以了解一下怎么计算农历日期的逻辑,另一方面也学习一下这类程序应该怎么写的。不过看作者自己把那么多的字符串接起来也真是不容易啊,佩服一个~
CREATE
TABLE
SolarData
(
YearID
INTEGER
NOT
NULL
,
--
农历年
DATA
CHAR
(
7
)
NOT
NULL
,
--
农历年对应的
16
进制数
DataInt
INTEGER
NOT
NULL
--
农历年对应的
10
进制数
);
INSERT
INTO
SolarData
VALUES
(
1900
,
'0x04bd8'
,
19416
);
INSERT
INTO
SolarData
VALUES
(
1901
,
'0x04ae0'
,
19168
);
INSERT
INTO
SolarData
VALUES
(
1902
,
'0x0a570'
,
42352
);
INSERT
INTO
SolarData
VALUES
(
1903
,
'0x054d5'
,
21717
);
INSERT
INTO
SolarData
VALUES
(
1904
,
'0x0d260'
,
53856
);
INSERT
INTO
SolarData
VALUES
(
1905
,
'0x0d950'
,
55632
);
INSERT
INTO
SolarData
VALUES
(
1906
,
'0x16554'
,
91476
);
INSERT
INTO
SolarData
VALUES
(
1907
,
'0x056a0'
,
22176
);
INSERT
INTO
SolarData
VALUES
(
1908
,
'0x09ad0'
,
39632
);
INSERT
INTO
SolarData
VALUES
(
1909
,
'0x055d2'
,
21970
);
INSERT
INTO
SolarData
VALUES
(
1910
,
'0x04ae0'
,
19168
);
INSERT
INTO
SolarData
VALUES
(
1911
,
'0x0a5b6'
,
42422
);
INSERT
INTO
SolarData
VALUES
(
1912
,
'0x0a4d0'
,
42192
);
INSERT
INTO
SolarData
VALUES
(
1913
,
'0x0d250'
,
53840
);
INSERT
INTO
SolarData
VALUES
(
1914
,
'0x1d255'
,
119381
);
INSERT
INTO
SolarData
VALUES
(
1915
,
'0x0b540'
,
46400
);
INSERT
INTO
SolarData
VALUES
(
1916
,
'0x0d6a0'
,
54944
);
INSERT
INTO
SolarData
VALUES
(
1917
,
'0x0ada2'
,
44450
);
INSERT
INTO
SolarData
VALUES
(
1918
,
'0x095b0'
,
38320
);
INSERT
INTO
SolarData
VALUES
(
1919
,
'0x14977'
,
84343
);
INSERT
INTO
SolarData
VALUES
(
1920
,
'0x04970'
,
18800
);
INSERT
INTO
SolarData
VALUES
(
1921
,
'0x0a4b0'
,
42160
);
INSERT
INTO
SolarData
VALUES
(
1922
,
'0x0b4b5'
,
46261
);
INSERT
INTO
SolarData
VALUES
(
1923
,
'0x06a50'
,
27216
);
INSERT
INTO
SolarData
VALUES
(
1924
,
'0x06d40'
,
27968
);
INSERT
INTO
SolarData
VALUES
(
1925
,
'0x1ab54'
,
109396
);
INSERT
INTO
SolarData
VALUES
(
1926
,
'0x02b60'
,
11104
);
INSERT
INTO
SolarData
VALUES
(
1927
,
'0x09570'
,
38256
);
INSERT
INTO
SolarData
VALUES
(
1928
,
'0x052f2'
,
21234
);
INSERT
INTO
SolarData
VALUES
(
1929
,
'0x04970'
,
18800
);
INSERT
INTO
SolarData
VALUES
(
1930
,
'0x06566'
,
25958
);
INSERT
INTO
SolarData
VALUES
(
1931
,
'0x0d4a0'
,
54432
);
INSERT
INTO
SolarData
VALUES
(
1932
,
'0x0ea50'
,
59984
);
INSERT
INTO
SolarData
VALUES
(
1933
,
'0x06e95'
,
28309
);
INSERT
INTO
SolarData
VALUES
(
1934
,
'0x05ad0'
,
23248
);
INSERT
INTO
SolarData
VALUES
(
1935
,
'0x02b60'
,
11104
);
INSERT
INTO
SolarData
VALUES
(
1936
,
'0x186e3'
,
100067
);
INSERT
INTO
SolarData
VALUES
(
1937
,
'0x092e0'
,
37600
);
INSERT
INTO
SolarData
VALUES
(
1938
,
'0x1c8d7'
,
116951
);
INSERT
INTO
SolarData
VALUES
(
1939
,
'0x0c950'
,
51536
);
INSERT
INTO
SolarData
VALUES
(
1940
,
'0x0d4a0'
,
54432
);
INSERT
INTO
SolarData
VALUES
(
1941
,
'0x1d8a6'
,
120998
);
INSERT
INTO
SolarData
VALUES
(
1942
,
'0x0b550'
,
46416
);
INSERT
INTO
SolarData
VALUES
(
1943
,
'0x056a0'
,
22176
);
INSERT
INTO
SolarData
VALUES
(
1944
,
'0x1a5b4'
,
107956
);
INSERT
INTO
SolarData
VALUES
(
1945
,
'0x025d0'
,
9680
);
INSERT
INTO
SolarData
VALUES
(
1946
,
'0x092d0'
,
37584
);
INSERT
INTO
SolarData
VALUES
(
1947
,
'0x0d2b2'
,
53938
);
INSERT
INTO
SolarData
VALUES
(
1948
,
'0x0a950'
,
43344
);
INSERT
INTO
SolarData
VALUES
(
1949
,
'0x0b557'
,
46423
);
INSERT
INTO
SolarData
VALUES
(
1950
,
'0x06ca0'
,
27808
);
INSERT
INTO
SolarData
VALUES
(
1951
,
'0x0b550'
,
46416
);
INSERT
INTO
SolarData
VALUES
(
1952
,
'0x15355'
,
86869
);
INSERT
INTO
SolarData
VALUES
(
1953
,
'0x04da0'
,
19872
);
INSERT
INTO
SolarData
VALUES
(
1954
,
'0x0a5d0'
,
42448
);
INSERT
INTO
SolarData
VALUES
(
1955
,
'0x14573'
,
83315
);
INSERT
INTO
SolarData
VALUES
(
1956
,
'0x052d0'
,
21200
);
INSERT
INTO
SolarData
VALUES
(
1957
,
'0x0a9a8'
,
43432
);
INSERT
INTO
SolarData
VALUES
(
1958
,
'0x0e950'
,
59728
);
INSERT
INTO
SolarData
VALUES
(
1959
,
'0x06aa0'
,
27296
);
INSERT
INTO
SolarData
VALUES
(
1960
,
'0x0aea6'
,
44710
);
INSERT
INTO
SolarData
VALUES
(
1961
,
'0x0ab50'
,
43856
);
INSERT
INTO
SolarData
VALUES
(
1962
,
'0x04b60'
,
19296
);
INSERT
INTO
SolarData
VALUES
(
1963
,
'0x0aae4'
,
43748
);
INSERT
INTO
SolarData
VALUES
(
1964
,
'0x0a570'
,
42352
);
INSERT
INTO
SolarData
VALUES
(
1965
,
'0x05260'
,
21088
);
INSERT
INTO
SolarData
VALUES
(
1966
,
'0x0f263'
,
62051
);
INSERT
INTO
SolarData
VALUES
(
1967
,
'0x0d950'
,
55632
);
INSERT
INTO
SolarData
VALUES
(
1968
,
'0x05b57'
,
23383
);
INSERT
INTO
SolarData
VALUES
(
1969
,
'0x056a0'
,
22176
);
INSERT
INTO
SolarData
VALUES
(
1970
,
'0x096d0'
,
38608
);
INSERT
INTO
SolarData
VALUES
(
1971
,
'0x04dd5'
,
19925
);
INSERT
INTO
SolarData
VALUES
(
1972
,
'0x04ad0'
,
19152
);
INSERT
INTO
SolarData
VALUES
(
1973
,
'0x0a4d0'
,
42192
);
INSERT
INTO
SolarData
VALUES
(
1974
,
'0x0d4d4'
,
54484
);
INSERT
INTO
SolarData
VALUES
(
1975
,
'0x0d250'
,
53840
);
INSERT
INTO
SolarData
VALUES
(
1976
,
'0x0d558'
,
54616
);
INSERT
INTO
SolarData
VALUES
(
1977
,
'0x0b540'
,
46400
);
INSERT
INTO
SolarData
VALUES
(
1978
,
'0x0b5a0'
,
46496
);
INSERT
INTO
SolarData
VALUES
(
1979
,
'0x195a6'
,
103846
);
INSERT
INTO
SolarData
VALUES
(
1980
,
'0x095b0'
,
38320
);
INSERT
INTO
SolarData
VALUES
(
1981
,
'0x049b0'
,
18864
);
INSERT
INTO
SolarData
VALUES
(
1982
,
'0x0a974'
,
43380
);
INSERT
INTO
SolarData
VALUES
(
1983
,
'0x0a4b0'
,
42160
);
INSERT
INTO
SolarData
VALUES
(
1984
,
'0x0b27a'
,
45690
);
INSERT
INTO
SolarData
VALUES
(
1985
,
'0x06a50'
,
27216
);
INSERT
INTO
SolarData
VALUES
(
1986
,
'0x06d40'
,
27968
);
INSERT
INTO
SolarData
VALUES
(
1987
,
'0x0af46'
,
44870
);
INSERT
INTO
SolarData
VALUES
(
1988
,
'0x0ab60'
,
43872
);
INSERT
INTO
SolarData
VALUES
(
1989
,
'0x09570'
,
38256
);
INSERT
INTO
SolarData
VALUES
(
1990
,
'0x04af5'
,
19189
);
INSERT
INTO
SolarData
VALUES
(
1991
,
'0x04970'
,
18800
);
INSERT
INTO
SolarData
VALUES
(
1992
,
'0x064b0'
,
25776
);
INSERT
INTO
SolarData
VALUES
(
1993
,
'0x074a3'
,
29859
);
INSERT
INTO
SolarData
VALUES
(
1994
,
'0x0ea50'
,
59984
);
INSERT
INTO
SolarData
VALUES
(
1995
,
'0x06b58'
,
27480
);
INSERT
INTO
SolarData
VALUES
(
1996
,
'0x055c0'
,
21952
);
INSERT
INTO
SolarData
VALUES
(
1997
,
'0x0ab60'
,
43872
);
INSERT
INTO
SolarData
VALUES
(
1998
,
'0x096d5'
,
38613
);
INSERT
INTO
SolarData
VALUES
(
1999
,
'0x092e0'
,
37600
);
INSERT
INTO
SolarData
VALUES
(
2000
,
'0x0c960'
,
51552
);
INSERT
INTO
SolarData
VALUES
(
2001
,
'0x0d954'
,
55636
);
INSERT
INTO
SolarData
VALUES
(
2002
,
'0x0d4a0'
,
54432
);
INSERT
INTO
SolarData
VALUES
(
2003
,
'0x0da50'
,
55888
);
INSERT
INTO
SolarData
VALUES
(
2004
,
'0x07552'
,
30034
);
INSERT
INTO
SolarData
VALUES
(
2005
,
'0x056a0'
,
22176
);
INSERT
INTO
SolarData
VALUES
(
2006
,
'0x0abb7'
,
43959
);
INSERT
INTO
SolarData
VALUES
(
2007
,
'0x025d0'
,
9680
);
INSERT
INTO
SolarData
VALUES
(
2008
,
'0x092d0'
,
37584
);
INSERT
INTO
SolarData
VALUES
(
2009
,
'0x0cab5'
,
51893
);
INSERT
INTO
SolarData
VALUES
(
2010
,
'0x0a950'
,
43344
);
INSERT
INTO
SolarData
VALUES
(
2011
,
'0x0b4a0'
,
46240
);
INSERT
INTO
SolarData
VALUES
(
2012
,
'0x0baa4'
,
47780
);
INSERT
INTO
SolarData
VALUES
(
2013
,
'0x0ad50'
,
44368
);
INSERT
INTO
SolarData
VALUES
(
2014
,
'0x055d9'
,
21977
);
INSERT
INTO
SolarData
VALUES
(
2015
,
'0x04ba0'
,
19360
);
INSERT
INTO
SolarData
VALUES
(
2016
,
'0x0a5b0'
,
42416
);
INSERT
INTO
SolarData
VALUES
(
2017
,
'0x15176'
,
86390
);
INSERT
INTO
SolarData
VALUES
(
2018
,
'0x052b0'
,
21168
);
INSERT
INTO
SolarData
VALUES
(
2019
,
'0x0a930'
,
43312
);
INSERT
INTO
SolarData
VALUES
(
2020
,
'0x07954'
,
31060
);
INSERT
INTO
SolarData
VALUES
(
2021
,
'0x06aa0'
,
27296
);
INSERT
INTO
SolarData
VALUES
(
2022
,
'0x0ad50'
,
44368
);
INSERT
INTO
SolarData
VALUES
(
2023
,
'0x05b52'
,
23378
);
INSERT
INTO
SolarData
VALUES
(
2024
,
'0x04b60'
,
19296
);
INSERT
INTO
SolarData
VALUES
(
2025
,
'0x0a6e6'
,
42726
);
INSERT
INTO
SolarData
VALUES
(
2026
,
'0x0a4e0'
,
42208
);
INSERT
INTO
SolarData
VALUES
(
2027
,
'0x0d260'
,
53856
);
INSERT
INTO
SolarData
VALUES
(
2028
,
'0x0ea65'
,
60005
);
INSERT
INTO
SolarData
VALUES
(
2029
,
'0x0d530'
,
54576
);
INSERT
INTO
SolarData
VALUES
(
2030
,
'0x05aa0'
,
23200
);
INSERT
INTO
SolarData
VALUES
(
2031
,
'0x076a3'
,
30371
);
INSERT
INTO
SolarData
VALUES
(
2032
,
'0x096d0'
,
38608
);
INSERT
INTO
SolarData
VALUES
(
2033
,
'0x04bd7'
,
19415
);
INSERT
INTO
SolarData
VALUES
(
2034
,
'0x04ad0'
,
19152
);
INSERT
INTO
SolarData
VALUES
(
2035
,
'0x0a4d0'
,
42192
);
INSERT
INTO
SolarData
VALUES
(
2036
,
'0x1d0b6'
,
118966
);
INSERT
INTO
SolarData
VALUES
(
2037
,
'0x0d250'
,
53840
);
INSERT
INTO
SolarData
VALUES
(
2038
,
'0x0d520'
,
54560
);
INSERT
INTO
SolarData
VALUES
(
2039
,
'0x0dd45'
,
56645
);
INSERT
INTO
SolarData
VALUES
(
2040
,
'0x0b5a0'
,
46496
);
INSERT
INTO
SolarData
VALUES
(
2041
,
'0x056d0'
,
22224
);
INSERT
INTO
SolarData
VALUES
(
2042
,
'0x055b2'
,
21938
);
INSERT
INTO
SolarData
VALUES
(
2043
,
'0x049b0'
,
18864
);
INSERT
INTO
SolarData
VALUES
(
2044
,
'0x0a577'
,
42359
);
INSERT
INTO
SolarData
VALUES
(
2045
,
'0x0a4b0'
,
42160
);
INSERT
INTO
SolarData
VALUES
(
2046
,
'0x0aa50'
,
43600
);
INSERT
INTO
SolarData
VALUES
(
2047
,
'0x1b255'
,
111189
);
INSERT
INTO
SolarData
VALUES
(
2048
,
'0x06d20'
,
27936
);
INSERT
INTO
SolarData
VALUES
(
2049
,
'0x0ada0'
,
44448
);
COMMIT
;
CREATE
OR
REPLACE
FUNCTION
f_GetLunar(i_SolarDay
DATE
)
RETURN
VARCHAR2
--
功能:计算阳历
1900/01/31 - 2050/01/22
间某一天对应的阴历是多少
--
算法:在一张表中用
10
进制格式保存某个农历年每月大小
,
有无闰月
,
闰月大小信息
-- 1.
用
12
个
2
进制位来表示某个农历年每月的大小,大月记为
1
,否则为
0
-- 2.
用低
4
位来表示闰月的月份,没有闰月记为
0
-- 3.
用一个高位表示闰月的大小,闰月大记为
0
,闰月小或无闰月记为
0
-- 4.
再将该
2
进制数转化为
10
进制,存入表中
--
农历
2000
年
: 0 110010010110 0000 -> 0x0c960 -> 51552
--
农历
2001
年
: 0 110110010101 0100 -> 0x0d954 -> 55636
--
采用查表的方式计算出农历日期
--
作者:
Angel_XJW
--
修改:
1.
-- 2.
AS
v_OffSet
INT
;
v_Lunar
INT
;
--
农历年是否含闰月
,
几月是闰月
,
闰月天数
,
其它月天数
v_YearDays
INT
;
--
农历年所含天数
v_MonthDays
INT
;
--
农历月所含天数
v_LeapMonthDays
INT
;
--
农历闰月所含天数
v_LeapMonth
INT
;
--
农历年闰哪个月
1-12 ,
没闰传回
0
v_LeapFlag
INT
;
--
某农历月是否为闰月
1:
是
0:
不是
v_MonthNo
INT
;
--
某农历月所对应的
2
进制数
如农历
3
月
: 001000000000
i
INT
;
j
INT
;
k
INT
;
v_Year
INT
;
-- i_SolarDay
对应的农历年
v_Month
INT
;
-- i_SolarDay
对应的农历月
v_Day
INT
;
-- i_SolarDay
对应的农历日
o_OutputDate
VARCHAR2
(
25
);
--
返回值
格式:农历
****
年
**(
闰
)
月
**
日
e_ErrMsg
VARCHAR2
(
200
);
e_ErrDate
EXCEPTION
;
BEGIN
--
输入参数判断
IF
i_SolarDay<TO_DATE(
'1900-01-31'
,
'YYYY-MM-DD'
)
OR
i_SolarDay>=TO_DATE(
'2050-01-23'
,
'YYYY-MM-DD'
)
THEN
RAISE
e_ErrDate;
END
IF
;
-- i_SolarDay
到
1900-01-30(
即农历
1900-01-01
的前一天
)
的天数
v_OffSet := TRUNC(i_SolarDay,
'DD'
) - TO_DATE(
'1900-01-30'
,
'YYYY-MM-DD'
);
--
确定农历年开始
i :=
1900
;
WHILE
i <
2050
AND
v_OffSet >
0
LOOP
v_YearDays :=
348
;
-- 29*12
以每年
12
个农历月
,
每个农历月含
29
个农历日为基数
v_LeapMonthDays :=
0
;
--
取出农历年是否含闰月
,
几月是闰月
,
闰月天数
,
其它月天数
--
如农历
2001
年
: 0x0d954(16
进制
) -> 55636(10
进制
) -> 0 110110010101 0100(2
进制
)
-- 1,2,4,5,8,10,12
月大
, 3,6,7,9,11
月小
, 4
月为闰月,闰月小
SELECT
DataInt
INTO
v_Lunar
FROM
SolarData
WHERE
YearId = i;
--
传回农历年的总天数
j :=
32768
;
-- 100000000000 0000 -> 32768
-- 0 110110010101 0100 -> 55636(
农历
2001
年
)
--
依次判断
v_Lunar
年个月是否为大月,是则加一天
WHILE
j >
8
LOOP
--
闰月另行判断
8 -> 0 000000000000 1000
IF
BITAND(v_Lunar, j) +
0
>
0
then
v_YearDays := v_YearDays +
1
;
END
IF
;
j := j/
2
;
--
判断下一个月是否为大
END
LOOP
;
--
传回农历年闰哪个月
1-12 ,
没闰传回
0 15 -> 1 0000
v_LeapMonth := BITAND(v_Lunar,
15
) +
0
;
--
传回农历年闰月的天数
,
加在年的总天数上
IF
v_LeapMonth >
0
THEN
--
判断闰月大小
65536 -> 1 000000000000 0000
IF
BITAND(v_Lunar,
65536
)+
0
>
0
THEN
v_LeapMonthDays :=
30
;
ELSE
v_LeapMonthDays :=
29
;
END
IF
;
v_YearDays := v_YearDays + v_LeapMonthDays;
END
IF
;
v_OffSet := v_OffSet - v_YearDays;
i := i +
1
;
END
LOOP
;
IF
v_OffSet <=
0
THEN
-- i_SolarDay
在所属农历年
(
即
i
年
)
中的第
v_OffSet
天
v_OffSet := v_OffSet + v_YearDays;
i := i -
1
;
END
IF
;
--
确定农历年结束
v_Year := i;
--
确定农历月开始
i :=
1
;
SELECT
DataInt
INTO
v_Lunar
FROM
SolarData
WHERE
YearId = v_Year;
--
判断那个月是润月
--
如农历
2001
年
(55636,15 -> 0 1101100101010100, 1111 -> 4)
即润
4
月
,
且闰月小
v_LeapMonth := BITAND(v_Lunar,
15
) +
0
;
v_LeapFlag :=
0
;
WHILE
i <
13
AND
v_OffSet >
0
LOOP
--
判断是否为闰月
v_MonthDays :=
0
;
IF
(v_LeapMonth >
0
AND
i = (v_LeapMonth +
1
)
AND
v_LeapFlag =
0
)
THEN
--
是闰月
i := i -
1
;
k := i;
--
保存是闰月的时
i
的值
v_LeapFlag :=
1
;
--
传回农历年闰月的天数
IF
BITAND(v_Lunar,
65536
)+
0
>
0
THEN
v_MonthDays :=
30
;
ELSE
v_MonthDays :=
29
;
END
IF
;
ELSE
--
不是闰月
j :=
1
;
v_MonthNo :=
65536
;
--
计算
i
月对应的
2
进制数
如农历
3
月
: 001000000000
WHILE
j<= i
LOOP
v_MonthNo := v_MonthNo/
2
;
j := j +
1
;
END
LOOP
;
--
计算农历
v_Year
年
i
月的天数
IF
BITAND(v_Lunar, v_MonthNo)+
0
>
0
THEN
v_MonthDays :=
30
;
ELSE
v_MonthDays :=
29
;
END
IF
;
END
IF
;
--
解除闰月
IF
v_LeapFlag =
1
AND
i = v_LeapMonth +
1
THEN
v_LeapFlag :=
0
;
END
IF
;
v_OffSet := v_OffSet - v_MonthDays;
i := i +
1
;
END
LOOP
;
IF
v_OffSet <=
0
THEN
-- i_SolarDay
在所属农历月
(
即
i
月
)
中的第
v_OffSet
天
v_OffSet := v_OffSet + v_MonthDays;
i := i -
1
;
END
IF
;
--
确定农历月结束
v_Month := i;
--
确定农历日结束
v_Day := v_OffSet;
--
格式化返回值
o_OutputDate :=
'
农历
'
||TO_CHAR(v_Year)||
'
年
'
;
IF
k = i
THEN
o_OutputDate := o_OutputDate || LPAD(TO_CHAR(v_Month),
2
,
'0'
)||
'(
润
)
月
'
;
ELSE
o_OutputDate := o_OutputDate || LPAD(TO_CHAR(v_Month),
2
,
'0'
)||
'
月
'
;
END
IF
;
o_OutputDate := o_OutputDate || LPAD(TO_CHAR(v_Day),
2
,
'0'
)||
'
日
'
;
RETURN
o_OutputDate;
EXCEPTION
WHEN
e_Errdate
THEN
RETURN
'
日期错误
!
有效范围
(
阳历
): 1900/01/31 - 2050/01/22'
;
WHEN
OTHERS
THEN
e_ErrMsg :=SUBSTR(
SQLERRM
,
1
,
200
);
RETURN
e_ErrMsg;
END
;
/