a(date1,date2)
date1-date2=天數
date1-date2=天數
解决方案 »
- oracle小问题
- 求救数据库回滚脚本怎么写?
- 如果抽取ORACLE日志中的数据?
- Oracle Migration Workbench問題
- 一个简单的问题^_^(在线等)
- 我想在LINUX下安装oracle ,安装linux的时候应该选择custom还是server?有什么好处?
- 请帮忙写一个SQL
- 真失败...关于优化pl/sql的问题
- ORA-01089:immediate shutdown in progress-no operations are permitted!
- 求助:如何退出存贮过程及FOR循环,从存贮过程中。
- 【help】怎样能取得数据库里当前最大的id号?怎样取得一段时间内新加入的数据?
- 各位,关于序列值的问题???
---------------------------
3
这样得到的是3,:(
----------------------------------
2003实际:270
12:41:10 SQL> select to_number(to_char(to_date('2003-06-18','yyyy-mm-dd'),'mm'))-1 from dual;TO_NUMBER(TO_CHAR(TO_DATE('2003-06-18','YYYY-MM-DD'),'MM'))-1
-------------------------------------------------------------
5实际:480
12:41:52 SQL> select to_number(to_char(to_date('2003-06-18','yyyy-mm-dd'),'dd'))-1 from dual;TO_NUMBER(TO_CHAR(TO_DATE('2003-06-18','YYYY-MM-DD'),'DD'))-1
-------------------------------------------------------------
17实际:100
12:42:17 SQL>
不行12:44:25 SQL> select to_date('2003-03-09')-to_date('2003-06-09') from dual;TO_DATE('2003-03-09')-TO_DATE('2003-06-09')
-------------------------------------------
-92实际:120
12:44:41 SQL>
使用pl/sql,to_char(date1,'yyyymmdd')字符截取,
输出年、月、日之差。
varchar2,
P_StartDate in Date,
P_EndDate in Date )
Return number
as
a number;
begin
if P_Type = 'year' then
a := extract(year from P_EndDate) - extract(year from P_StartDate);
elsif P_Type = 'yyyy' then
a := extract(year from P_EndDate) - extract(year from P_StartDate);
ELSIF P_Type = 'yy' then
a := extract(year from P_EndDate) - extract(year from P_StartDate);
elsif P_Type = 'month' then
a := Round((round(p_EndDate,'month')-round(p_StartDate,'month'))/30);
elsif P_Type = 'mm' then
a := Round((round(p_EndDate,'mm')-round(p_StartDate,'mm'))/30);
elsif P_Type = 'm' then
a := Round((round(p_EndDate,'m')-round(p_StartDate,'m'))/30);
elsif P_Type = 'Day' then
a := Round(P_EndDate,'day')-round(P_StartDate,'day');
elsif P_Type = 'dd' then
a := Round(P_EndDate,'dd')-round(P_StartDate,'dd');
elsif P_type = 'd' then
a := Round(P_EndDate,'d')-round(P_StartDate,'d');
end if;
return a;
end;