ORACLE的SQL练习---6. 利用oracle日期函数制作日期维度表
程序员文章站
2022-03-31 19:10:07
...
oracle常用的日期函数：
sysdate
【功能】：返回当前日期。
【参数】：没有参数，没有括号
【返回】：日期
【示例】select sysdate from dual;
返回：2020/11/26 10:04:37
add_months(d1,n1)
【功能】：返回在日期d1基础上再加n1个月后新的日期。
【参数】：d1，日期型，n1数字型
【返回】：日期
【示例】select sysdate,add_months(sysdate,3) hz from dual;
返回： 2020/11/26 10:05:25 2021/2/26 10:05:25
last_day(d1)
【功能】：返回日期d1所在月份最后一天的日期。
【参数】：d1，日期型
【返回】：日期
【示例】select sysdate,last_day(sysdate) hz from dual;
返回： 2020/11/26 10:06:27 2020/11/30 10:06:27
months_between(d1,d2)
【功能】：返回日期d1到日期d2之间的月数。
【参数】：d1，d2 日期型
【返回】：数字
如果d1>d2，则返回正数
如果d1<d2，则返回负数
【示例】
select sysdate,
months_between(sysdate,to_date('2020-01-01','YYYY-MM-DD')),
months_between(sysdate,to_date('2022-01-01','YYYY-MM-DD')) from dual;
返回： 2020/11/26 10:07:43 10.8200653375149 -13.1799346624851
round(d1[,c1])
【功能】：给出日期d1按期间(参数c1)四舍五入后的期间的第一天日期（与数值四舍五入意思相近）
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即最近0点日期)
【返回】：日期
【示例】
select sysdate 当时日期,
round(sysdate) 最近0点日期,
round(sysdate,'day') 最近星期日,
round(sysdate,'month') 最近月初,
round(sysdate,'q') 最近季初日期,
round(sysdate,'year') 最近年初日期 from dual;
trunc(d1[,c1])
【功能】：返回日期d1所在期间(参数c1)的第一天日期
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即当前日期)
【返回】：日期
【示例】
select sysdate 当时日期,
trunc(sysdate) 今天日期,
trunc(sysdate,'day') 本周星期日,
trunc(sysdate,'month') 本月初,
trunc(sysdate,'q') 本季初日期,
trunc(sysdate,'year') 本年初日期 from dual;
利用ORACLE日期函数构建数仓的日期维度表：
我们在实际的工作中经常要做很多日期的处理，比如做同期、上期、上月、上季度等等的数据对比。每次取数都要加一堆的函数处理很繁琐。把经常用到的这些信息做一张日期维度，就可以取出对应的日期。
例如下表从日、昨日、周、旬、月、季度、半年、年的各种日期粒度来设计：
具体语句如下：
--create table t_dwd_date as
SELECT to_number(TO_CHAR(TRUNC(sd + rn), 'YYYYMMDD')) day_id,---日期ID
TRUNC(sd + rn) day_date,--日期格式
to_char(sd + rn, 'YYYY-MM-DD') DAY_STR,--10位字符串格式
f_GetLunar(sd + rn) as nl,--农历（自定义函数）
to_char(sd + rn,'day') week_cn,--星期
to_char(sd + rn,'day','NLS_DATE_LANGUAGE=AMERICAN') week_en ,--星期英文格式
case when to_char(sd + rn,'day','NLS_DATE_LANGUAGE=AMERICAN') in ('saturday ','sunday') then '1' else '0' end as if_weekend,---周末的判断
to_number(TO_CHAR(sd + rn, 'DD')) DAY_OF_MONTH,--本月的第几天
to_number(TO_CHAR(sd + rn, 'DDD')) DAY_OF_YEAR,---本年的第几天
to_number(TO_CHAR(TRUNC(sd + rn - 1), 'YYYYMMDD')) pre_day_id,---昨日
to_number(TO_CHAR(add_months(sd + rn, -1), 'YYYYMMDD')) LM_DAY_ID,--上月同期
to_number(TO_CHAR(add_months(sd + rn, -3), 'YYYYMMDD')) LQ_DAY_ID,--上季度同期
to_number(TO_CHAR(add_months(sd + rn, -12), 'YYYYMMDD')) LY_DAY_ID,--上年同期
to_number(TO_CHAR(add_months((TRUNC(sd + rn, 'MM')), -1), 'YYYYMMDD')) LM_START_DAY_ID,--上月第一天
to_number(TO_CHAR(add_months((TRUNC(sd + rn, 'Q')), -3), 'YYYYMMDD')) LQ_START_DAY_ID,--上季度第一天
to_number(TO_CHAR(add_months((TRUNC(sd + rn, 'YEAR')), -12), 'YYYYMMDD')) LY_START_DAY_ID,--上年第一天
to_number(TO_CHAR((TRUNC(sd + rn, 'MM')), 'YYYYMMDD')) CM_START_DAY_ID,--当月第一天
to_number(TO_CHAR((TRUNC(sd + rn, 'Q')), 'YYYYMMDD')) CQ_START_DAY_ID,--当季度第一天
to_number(TO_CHAR((TRUNC(sd + rn, 'YEAR')), 'YYYYMMDD')) CY_START_DAY_ID, --本年第一天
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)) TENDAY_ID,--旬
TRUNC(sd + rn, 'MM') +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
20,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10) * 10) TENDAY_START_DATE,--本旬第一天
decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)),
3,
last_day(sd + rn),
TRUNC(sd + rn, 'MM') + 9 +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
20,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10) * 10)) TENDAY_END_DATE,--本旬最后一天
TO_CHAR(sd + rn, 'YYYY-MM') ||
decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)),
1,
'上旬',
2,
'中旬',
3,
'下旬') TENDAY_CN_DESC,---旬 中文描述
decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)),
1,
'1st',
2,
'2nd',
3,
'3rd') || ' tenday of ' ||
to_char(sd + rn, 'MON,YYYY', 'nls_date_language = AMERICAN') TENDAY_EN_DESC, --旬英文描述
(decode(decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
3,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10 + 1)),
3,
last_day(sd + rn),
TRUNC(sd + rn, 'MM') + 9 +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
20,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10) * 10))) -
(TRUNC(sd + rn, 'MM') +
decode(TO_number(TO_CHAR(sd + rn, 'DD')),
31,
20,
trunc((TO_number(TO_CHAR(sd + rn, 'DD')) - 1) / 10) * 10)) + 1 TENDAY_DURATION, --旬天数
to_number(TO_CHAR(TRUNC(sd + rn), 'YYYYMM')) month_id,---月份
TRUNC(sd + rn, 'MM') MONTH_START_DATE, --本月第一天（日期类型）
last_day(sd + rn) MONTH_END_DATE,--本月最后一天（日期类型）
to_char(sd + rn, 'YYYY-MM') MONTH_CN_DESC,---YYYY-MM格式
to_char(sd + rn, 'MON YYYY', 'nls_date_language = AMERICAN') MONTH_EN_DESC,---英文格式
to_number(TO_CHAR(sd + rn, 'MM')) MONTH_OF_YEAR,---当前月
last_day(sd + rn) -
to_date(TO_CHAR(TRUNC(sd + rn), 'YYYYMM') || '01', 'YYYY/MM/DD') + 1 MONTH_DURATION,---本月天数
to_number(TO_CHAR(sd + rn, 'YYYYQ')) QTR_ID,---季度ID
to_number(TO_CHAR(sd + rn, 'Q')) QTR_OF_YEAR,--季度数
TRUNC(sd + rn, 'Q') QTR_START_DATE,--季度起始日期
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
TO_DATE('03/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
TO_DATE('06/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
TO_DATE('09/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
END) QTR_END_DATE,--季度终止日期
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
to_char(sd + rn, 'YYYY') || '-1季度'
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
to_char(sd + rn, 'YYYY') || '-2季度'
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
to_char(sd + rn, 'YYYY') || '-3季度'
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
to_char(sd + rn, 'YYYY') || '-4季度'
END) QTR_CN_DESC,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
'QTR 1,' || to_char(sd + rn, 'YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
'QTR 2,' || to_char(sd + rn, 'YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
'QTR 3,' || to_char(sd + rn, 'YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
'QTR 4,' || to_char(sd + rn, 'YYYY')
END) QTR_EN_DESC,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
TO_DATE('03/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
TO_DATE('06/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
TO_DATE('09/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
END) - TRUNC(sd + rn, 'Q') + 1 QTR_DURATION,---季度天数
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
to_number(to_char(sd + rn, 'YYYY') || 1)
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
to_number(to_char(sd + rn, 'YYYY') || 1)
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
to_number(to_char(sd + rn, 'YYYY') || 2)
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
to_number(to_char(sd + rn, 'YYYY') || 2)
END) HFYR_ID,----半年ID
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
TO_DATE('01/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
TO_DATE('01/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
TO_DATE('07/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
TO_DATE('07/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
END) HFYR_START_DATE,----半年起始日期
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
TO_DATE('06/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
TO_DATE('06/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
END) HFYR_END_DATE,----半年终止日期
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
to_char(sd + rn, 'YYYY') || '上半年'
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
to_char(sd + rn, 'YYYY') || '上半年'
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
to_char(sd + rn, 'YYYY') || '下半年'
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
to_char(sd + rn, 'YYYY') || '下半年'
END) HFYR_CN_DESC,---上下半年
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
'1st Half Year of ' || to_char(sd + rn, 'YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
'1st Half Year of ' || to_char(sd + rn, 'YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
'2st Half Year of ' || to_char(sd + rn, 'YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
'2st Half Year of ' || to_char(sd + rn, 'YYYY')
END) HFYR_EN_DESC,
(CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
TO_DATE('06/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
TO_DATE('06/30/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
END) - (CASE
WHEN TO_CHAR(sd + rn, 'Q') = 1 THEN
TO_DATE('01/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 2 THEN
TO_DATE('01/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 3 THEN
TO_DATE('07/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
WHEN TO_CHAR(sd + rn, 'Q') = 4 THEN
TO_DATE('07/01/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY')
END) + 1 HFYR_DURATION,---半年天数
to_number(TO_CHAR(TRUNC(sd + rn), 'YYYY')) YEAR_ID,----年ID
TRUNC(sd + rn, 'YEAR') YEAR_START_DATE,---本年第一天
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY') YEAR_END_DATE,----本年最后一天
TO_CHAR(TRUNC(sd + rn), 'YYYY') YEAR_CN_DESC,
TO_CHAR(TRUNC(sd + rn), 'YYYY') YEAR_EN_DESC,
TO_DATE('12/31/' || TO_CHAR(sd + rn, 'YYYY'), 'MM/DD/YYYY') -
TRUNC(sd + rn, 'YEAR') + 1 YEAR_DURATION,---本年天数
to_number(to_char(add_months(sd + rn, -12), 'YYYY')) PREV_YEAR_ID,---上年ID
to_number(TO_CHAR(TRUNC(sd + rn), 'YYYY'))||'年'||to_number(TO_CHAR(sd + rn, 'MM'))||'月' MONTH_DESC,
to_number(TO_CHAR(TRUNC(sd + rn), 'YYYY'))||'年' YEAR_ONLY,
to_number(TO_CHAR(sd + rn, 'MM'))||'月' MONTH_ONLY,
to_number(TO_CHAR(sd + rn, 'W')) week_of_month,---当日属于本月第几周
to_number(TO_CHAR(sd + rn, 'WW')) week_of_year,--当日属于本年第几周
to_number(TO_CHAR(TRUNC(sd + rn), 'YYYY'))||'年第'||to_number(TO_CHAR(sd + rn, 'WW'))||'周' week_of_year_desc
FROM (SELECT TO_DATE('10/31/2020', 'MM/DD/YYYY') sd, rownum rn
FROM dual
CONNECT BY level <= 100)
其中农历的处理来自论坛里某位大神的杰作，这里引用了一下
http://www.itpub.net/thread-509307-1-1.html
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;
自己能写出上面的语句之后，就基本掌握了常用的日期函数。
上一篇： 解决.Net服务器发送HTTP后设置不了内容类型的情况
下一篇： php字符轮换