oracle农历转公历 转换就是先建一个对照表(年,农历月,初一对应的日期), 先用农历的年、月查到对应的日期,然后再加上 (农历日-1) 天。 又:农历的闰月呢?怎么表示? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 明天有时间的话给你写一个吧,目前手上只有.net版阴历阳历转换的类,没有现成函数 FUNCTION GetCalendarDate(P_Year in VARCHAR2, P_Month IN VARCHAR2, P_Day IN VARCHAR2, P_LeepFlag IN Boolean := False)RETURN date IS YearSetData VARCHAR2(32767):= '010010111101080131,010010101110000219,101001010111000208,010101001101050129,110100100110000216,110110010101000204,011001010101140125,010101101010000213,100110101101000202,010101011101020122,'|| '010010101110000210,101001011011060130,101001001101000218,110100100101000206,110100100101150126,101101010101000214,010101101010000204,101011011010020123,100101011011000211,010010010111170201,'|| '010010011011000220,101001001011000208,101101001011050128,011010100101000216,011011010100000205,101010110101140124,001010110110000213,100101010111000202,010100101111020123,010010010111000210,'|| '011001010110060130,110101001010000217,111010100101000206,011010101001150126,010110101101000214,001010110110000204,100001101110130124,100100101110000211,110010001101170131,110010010101000219,'|| '110101001010000208,110110001010160127,101101010101000215,010101101010000205,101001011011140125,001001011101000213,100100101101000202,110100101011020122,101010010101000210,101101010101070129,'|| '011011001010000217,101101010101000206,010100110101150127,010011011010000214,101001011011000203,010001010111130124,010100101011000212,101010011010080131,111010010101000218,011010101010000208,'|| '101011101010060128,101010110101000215,010010110110000205,101010101110040125,101001010111000213,010100100110000202,111100100110030121,110110010101000209,010110110101070130,010101101010000217,'|| '100101101101000206,010011011101050127,010010101101000215,101001001101000203,110101001101040123,110100100101000211,110101010101080131,101101010100000218,101101101010000207,100101011010160128,'|| '100101011011000216,010010011011000205,101010010111040125,101001001011000213,1011001001110A0202,011010100101000220,011011010100000209,101011110100060129,101010110110000217,100101010111000206,'|| '010010101111050127,010010010111000215,011001001011000204,011101001010030123,111010100101000210,011010110101080131,010110101100000219,101010110110000207,100101101101050128,100100101110000216,'|| '110010010110000205,110110010101040124,110101001010000212,110110100101000201,011101010101020122,010101101010000209,101010111011070129,001001011101000218,100100101101000207,110010101011050126,'|| '101010010101000214,101101001010000203,101110101010040123,101011010101000210,010101011101090131,010010111010000219,101001011011000208,010100010111160128,010100101011000216,101010010011000205,'|| '011110010101040125,011010101010000212,101011010101000201,010110110101020122,010010110110000210,100101101110060129,101001001110000217,110100100110000206,111010100110050126,110101010011000213,'|| '010110101010000203,011101101010030123,100101101101000211,0100101010110B0131,010010101101000219,101001001101000208,110100001011160128,110100100101000215,110101010010000204,110111010100050124,'|| '101101011010000212,010101101101000201,010101011011020122,010010011011000210,101001010111070130,101001001011000217,101010100101000206,101100100101150126,011011010010000214,101011011010000202,'|| '010010110110130123,100100110111000211,010010011111080201,010010010111000219,011001001011000208,011010001010160128,111010100101000215,011010101010000204,101001101100140124,101010101110000212,'|| '100100101110000202,110100101110030121,110010010110000209,110101010101070129,110101001010000217,110110100100000205,010111010101050126,010101101010000214,101001101100000203,010101011101040123,'|| '010100101101000211,101010011011080131,101010010101000219,101101001010000207,101101101010060127,101011010101000215,010101011010000205,101010111010040124,101001011010000212,010100101011000202,'|| '101100100111030122,011010010011000209,011100110011070129,011010101010000217,101011010101000206,010010110101150126,010010110110000214,101001010111000203,010101001110040124,110100010110000210,'|| '111010010110080130,110101010010000218,110110101010000207,011010101010160127,010101101101000215,010010101110000205,101010011101040125,101000101101000212,110100010101000201,111100100101020121,110101010010000209'; V_SpringFestival DATE;--春节日期 V_YearString VARCHAR2(20);--年度设置字符串 V_LeepMonth number:=0; V_MonthDays number; V_MonthIndex number; i number; --循环变量BEGIN If P_Year < 1900 OR P_Year > 2100 Then Return NULL; END IF;--此函数仅支持1900~2100年,其他年份返回空值 V_YearString:=SUBSTR(YearSetData,(P_Year - 1900) * 19+1, 18); V_SpringFestival := TO_Date(P_Year||SUBSTR(V_YearString, 15, 4),'YYYYMMDD'); V_LeepMonth := TO_NUMBER(SUBSTR(V_YearString, 14, 1),'XX'); V_MonthIndex := P_Month; If V_LeepMonth > 0 Then --有闰月 V_YearString := SUBSTR(V_YearString,1,V_LeepMonth)||SUBSTR(V_YearString, 13, 1)||substr(V_YearString, V_LeepMonth + 1); If (P_Month = V_LeepMonth AND P_LeepFlag) OR P_Month > V_LeepMonth Then V_MonthIndex:=V_MonthIndex+ 1; End If; End If; If P_Day ='30' And SUBSTR(V_YearString, V_MonthIndex, 1) = '0' Then RETURN NULL; END IF;--本年的这个月份没有30日 RETURN V_SpringFestival+29*(V_MonthIndex - 1)+REGEXP_COUNT(SUBSTR(V_YearString,1,V_MonthIndex - 1), '1')+to_number(P_DAY)-1;END; 使用方法V_DATE:= GetCalendarDate('2014','09','05',TRUE);--2014年闰九月初五V_DATE:= GetCalendarDate('2014','09','05');--2014年九月初五由于参数类型中含有BOOLEAN类型,因此在DML语句中,不能直接使用可以根据需要调整参数类型,如用数字0、1代表TRUE、FALSE,就可以使用了 关于导入导出大字段的问题 oracle11g 远程访问EM数据库管理平台无法连接(本机是LINUX系统) 如何提高查询速度:有个查询晚上运行只要1分钟,但白天要1个多小时(此时并发用户50多个)? 求一条“迭代更新”的SQL 如果只有表空间能恢复库吗? 我查不出我想要的表 问个查询的问题 我是新手,向各位大侠请教一个问题:怎样在oracle 定义一个游标? 如何学习OCI 如何创建一个用户只能以normal模式登陆,不能以sysdba和sysoper登陆OEM EXEC SQL EXECUTE 咨询 为啥删了个文件就不能运行了,求大家帮忙解决下
P_Month IN VARCHAR2,
P_Day IN VARCHAR2,
P_LeepFlag IN Boolean := False)
RETURN date IS
YearSetData VARCHAR2(32767):=
'010010111101080131,010010101110000219,101001010111000208,010101001101050129,110100100110000216,110110010101000204,011001010101140125,010101101010000213,100110101101000202,010101011101020122,'||
'010010101110000210,101001011011060130,101001001101000218,110100100101000206,110100100101150126,101101010101000214,010101101010000204,101011011010020123,100101011011000211,010010010111170201,'||
'010010011011000220,101001001011000208,101101001011050128,011010100101000216,011011010100000205,101010110101140124,001010110110000213,100101010111000202,010100101111020123,010010010111000210,'||
'011001010110060130,110101001010000217,111010100101000206,011010101001150126,010110101101000214,001010110110000204,100001101110130124,100100101110000211,110010001101170131,110010010101000219,'||
'110101001010000208,110110001010160127,101101010101000215,010101101010000205,101001011011140125,001001011101000213,100100101101000202,110100101011020122,101010010101000210,101101010101070129,'||
'011011001010000217,101101010101000206,010100110101150127,010011011010000214,101001011011000203,010001010111130124,010100101011000212,101010011010080131,111010010101000218,011010101010000208,'||
'101011101010060128,101010110101000215,010010110110000205,101010101110040125,101001010111000213,010100100110000202,111100100110030121,110110010101000209,010110110101070130,010101101010000217,'||
'100101101101000206,010011011101050127,010010101101000215,101001001101000203,110101001101040123,110100100101000211,110101010101080131,101101010100000218,101101101010000207,100101011010160128,'||
'100101011011000216,010010011011000205,101010010111040125,101001001011000213,1011001001110A0202,011010100101000220,011011010100000209,101011110100060129,101010110110000217,100101010111000206,'||
'010010101111050127,010010010111000215,011001001011000204,011101001010030123,111010100101000210,011010110101080131,010110101100000219,101010110110000207,100101101101050128,100100101110000216,'||
'110010010110000205,110110010101040124,110101001010000212,110110100101000201,011101010101020122,010101101010000209,101010111011070129,001001011101000218,100100101101000207,110010101011050126,'||
'101010010101000214,101101001010000203,101110101010040123,101011010101000210,010101011101090131,010010111010000219,101001011011000208,010100010111160128,010100101011000216,101010010011000205,'||
'011110010101040125,011010101010000212,101011010101000201,010110110101020122,010010110110000210,100101101110060129,101001001110000217,110100100110000206,111010100110050126,110101010011000213,'||
'010110101010000203,011101101010030123,100101101101000211,0100101010110B0131,010010101101000219,101001001101000208,110100001011160128,110100100101000215,110101010010000204,110111010100050124,'||
'101101011010000212,010101101101000201,010101011011020122,010010011011000210,101001010111070130,101001001011000217,101010100101000206,101100100101150126,011011010010000214,101011011010000202,'||
'010010110110130123,100100110111000211,010010011111080201,010010010111000219,011001001011000208,011010001010160128,111010100101000215,011010101010000204,101001101100140124,101010101110000212,'||
'100100101110000202,110100101110030121,110010010110000209,110101010101070129,110101001010000217,110110100100000205,010111010101050126,010101101010000214,101001101100000203,010101011101040123,'||
'010100101101000211,101010011011080131,101010010101000219,101101001010000207,101101101010060127,101011010101000215,010101011010000205,101010111010040124,101001011010000212,010100101011000202,'||
'101100100111030122,011010010011000209,011100110011070129,011010101010000217,101011010101000206,010010110101150126,010010110110000214,101001010111000203,010101001110040124,110100010110000210,'||
'111010010110080130,110101010010000218,110110101010000207,011010101010160127,010101101101000215,010010101110000205,101010011101040125,101000101101000212,110100010101000201,111100100101020121,110101010010000209';
V_SpringFestival DATE;--春节日期
V_YearString VARCHAR2(20);--年度设置字符串
V_LeepMonth number:=0;
V_MonthDays number;
V_MonthIndex number;
i number; --循环变量
BEGIN
If P_Year < 1900 OR P_Year > 2100 Then Return NULL; END IF;--此函数仅支持1900~2100年,其他年份返回空值
V_YearString:=SUBSTR(YearSetData,(P_Year - 1900) * 19+1, 18);
V_SpringFestival := TO_Date(P_Year||SUBSTR(V_YearString, 15, 4),'YYYYMMDD');
V_LeepMonth := TO_NUMBER(SUBSTR(V_YearString, 14, 1),'XX');
V_MonthIndex := P_Month;
If V_LeepMonth > 0 Then --有闰月
V_YearString := SUBSTR(V_YearString,1,V_LeepMonth)||SUBSTR(V_YearString, 13, 1)||substr(V_YearString, V_LeepMonth + 1);
If (P_Month = V_LeepMonth AND P_LeepFlag) OR P_Month > V_LeepMonth Then
V_MonthIndex:=V_MonthIndex+ 1;
End If;
End If;
If P_Day ='30' And SUBSTR(V_YearString, V_MonthIndex, 1) = '0' Then RETURN NULL; END IF;--本年的这个月份没有30日
RETURN V_SpringFestival+29*(V_MonthIndex - 1)+REGEXP_COUNT(SUBSTR(V_YearString,1,V_MonthIndex - 1), '1')+to_number(P_DAY)-1;
END;
V_DATE:= GetCalendarDate('2014','09','05',TRUE);--2014年闰九月初五
V_DATE:= GetCalendarDate('2014','09','05');--2014年九月初五
由于参数类型中含有BOOLEAN类型,因此在DML语句中,不能直接使用
可以根据需要调整参数类型,如用数字0、1代表TRUE、FALSE,就可以使用了