转贴：oracle日期处理完全版

转贴：oracle日期处理完全版

TO_DATE格式

复制内容到剪贴板

代码:

Day:
dd number 12
dy abbreviated fri
day spelled out friday
ddspth spelled out, ordinal twelfth
Month:
mm number 03
mon abbreviated mar
month spelled out march
Year:
yy two digits 98
yyyy four digits 1998

24小时格式下时间范围为： 0:00:00 - 23:59:59....
12小时格式下时间范围为： 1:00:00 - 12:59:59 ....


1.
日期和字符转换函数用法（to_date,to_char）


2.

复制内容到剪贴板

代码:

select to_char( to_date(222,'J'),'Jsp') from dual

显示Two Hundred Twenty-Two

3.
求某天是星期几

复制内容到剪贴板

代码:

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;

星期一

复制内容到剪贴板

代码:

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
monday

设置日期语言

复制内容到剪贴板

代码:

ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';

也可以这样

复制内容到剪贴板

代码:

TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')

4.
两个日期间的天数

复制内容到剪贴板

代码:

select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;

5. 时间为null的用法

复制内容到剪贴板

代码:

select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;

注意要用TO_DATE(null)

6.

复制内容到剪贴板

代码:

a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')

那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
所以，当时间需要精确的时候，觉得to_char还是必要的
7. 日期格式冲突问题
输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'

复制内容到剪贴板

代码:

alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American

或者在to_date中写

复制内容到剪贴板

代码:

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;

注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，
可查看

复制内容到剪贴板

代码:

select * from nls_session_parameters
select * from V$NLS_PARAMETERS

8.

复制内容到剪贴板

代码:

select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1
)
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )
not
in ( '1', '7' )

查找2002-02-28至2002-02-01间除星期一和七的天数
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).

9.

复制内容到剪贴板

代码:

select months_between(to_date('01-31-1999','MM-DD-YYYY'),
to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;

1

复制内容到剪贴板

代码:

select months_between(to_date('02-01-1999','MM-DD-YYYY'),
to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;

1.03225806451613
10. Next_day的用法

复制内容到剪贴板

代码:

Next_day(date, day)

Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D

11

复制内容到剪贴板

代码:

select to_char(sysdate,'hh:mi:ss') TIME from all_objects

注意：第一条记录的TIME 与最后一行是一样的
可以建立一个函数来处理这个问题

复制内容到剪贴板

代码:

create or replace function sys_date return date is
begin
return sysdate;
end;

select to_char(sys_date,'hh:mi:ss') from all_objects;

12.
获得小时数

复制内容到剪贴板

代码:

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer
SQL> select sysdate ,to_char(sysdate,'hh') from dual;

SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07

复制内容到剪贴板

代码:

SQL> select sysdate ,to_char(sysdate,'hh24') from dual;

SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19

获取年月日与此类似
13.

年月日的处理

复制内容到剪贴板

代码:

select older_date,
newer_date,
years,
months,
abs(
trunc(
newer_date-
add_months( older_date,years*12+months )
)
) days
from ( select
trunc(months_between( newer_date, older_date )/12) YEARS,
mod(trunc(months_between( newer_date, older_date )),
12 ) MONTHS,
newer_date,
older_date
from ( select hiredate older_date,
add_months(hiredate,rownum)+rownum newer_date
from emp )
)

14.
处理月份天数不定的办法

复制内容到剪贴板

代码:

select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual

16.
找出今年的天数

复制内容到剪贴板

代码:

select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual

闰年的处理方法

复制内容到剪贴板

代码:

to_char( last_day( to_date('02' || :year,'mmyyyy') ), 'dd' )

如果是28就不是闰年

17.
yyyy与rrrr的区别
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001

18.不同时区的处理

复制内容到剪贴板

代码:

select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate
from dual;

19.
5秒钟一个间隔

复制内容到剪贴板

代码:

Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')
from dual

2002-11-1 9:55:00 35786
SSSSS表示5位秒数

20.
一年的第几天

复制内容到剪贴板

代码:

select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
310 2002-11-6 10:03:51

21.计算小时,分,秒,毫秒

复制内容到剪贴板

代码:

select
Days,
A,
TRUNC(A*24) Hours,
TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,
TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,
TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
from
(
select
trunc(sysdate) Days,
sysdate - trunc(sysdate) A
from dual
)



select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');

//
floor((date2-date1) /365) 作为年
floor((date2-date1, 365) /30) 作为月
mod(mod(date2-date1, 365), 30)作为日.


23.next_day函数
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。

心生则种种魔生；心灭则种种魔灭

查看详细资料

TOP

洁净水源 德国世保康直饮水净化器 甘泉热线：0563-3019369，13966197369 维修：13965667864

DamYankee

VIP

老马

UID

476 

精华

38 

积分

17998 

功德

483 金豆 

来自

宣城 

• 个人空间
• 发短消息
• 加为好友
• 当前离线

2^# 大 中 小 发表于 2006-4-24 08:25  只看该作者

SQL语句优化技术分析





操作符优化
IN 操作符
       用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：
       ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。
       推荐方案：在业务密集的SQL当中尽量不采用IN操作符。

NOT IN操作符
      此操作是强列推荐不使用的，因为它不能应用表的索引。
       推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替

操作符（不等于）
       不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。
推荐方案：用其它相同功能的操作运算代替，如
       a0 改为 a>0 or a
       a’’ 改为 a>’’

IS NULL 或IS NOT NULL操作（判断字段是否为空）
       判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。
       推荐方案：
用其它相同功能的操作运算代替，如
       a is not null 改为 a>0 或a>’’等。
       不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。
       建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）

> 及
       大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

LIKE操作符
      LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。

UNION操作符
      UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：
select * from gc_dfys
union
select * from ls_jg_dfys
      这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。
      推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。
select * from gc_dfys
union all
select * from ls_jg_dfys

SQL书写的影响
同一功能同一性能不同写法SQL的影响
如一个SQL在A程序员写的为
       Select * from zl_yhjbqk
B程序员写的为
       Select * from dlyx.zl_yhjbqk（带表所有者的前缀）
C程序员写的为
       Select * from DLYX.ZLYHJBQK（大写表名）
D程序员写的为
       Select *  from DLYX.ZLYHJBQK（中间多了空格）
      以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。

WHERE后面的条件顺序影响
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1  and dy_dj = '1KV以下'
以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。

查询表顺序的影响
      在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接，由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。（注：如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接）

SQL语句索引的利用
对操作符的优化（见上节）
对条件字段的一些优化
采用函数处理的字段不能利用索引，如：
substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’
trunc(sk_rq)=trunc(sysdate)， 优化处理：
sk_rq>=trunc(sysdate) and sk_rq
进行了显式或隐式的运算的字段不能进行索引，如：
ss_df+20>50，优化处理：ss_df>30
‘X’||hbs_bh>’X5400021452’，优化处理：hbs_bh>’5400021542’
sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5
hbs_bh=5401002554，优化处理：hbs_bh=’ 5401002554’，注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型。
条件内包括了多个本表的字段运算时不能进行索引，如：
ys_df>cx_df，无法进行优化
qc_bh||kh_bh=’5400250000’，优化处理：qc_bh=’5400’ and kh_bh=’250000’

应用ORACLE的HINT（提示）处理
      提示处理是在ORACLE产生的SQL分析执行路径不满意的情况下要用到的。它可以对SQL进行以下方面的提示
目标方面的提示：
COST（按成本优化）
RULE（按规则优化）
CHOOSE（缺省）（ORACLE自动选择成本或规则进行优化）
ALL_ROWS（所有的行尽快返回）
FIRST_ROWS（第一行数据尽快返回）
执行方法的提示：
USE_NL（使用NESTED LOOPS方式联合）
USE_MERGE（使用MERGE JOIN方式联合）
USE_HASH（使用HASH JOIN方式联合）
索引提示：
INDEX（TABLE INDEX）（使用提示的表索引进行查询）
其它高级提示（如并行处理等等）
      ORACLE的提示功能是比较强的功能，也是比较复杂的应用，并且提示只是给ORACLE执行的一个建议，有时如果出于成本方面的考虑ORACLE也可能不会按提示进行。根据实践应用，一般不建议开发人员应用ORACLE提示，因为各个数据库及服务器性能情况不一样，很可能一个地方性能提升了，但另一个地方却下降了，ORACLE在SQL执行分析方面已经比较成熟，如果分析执行的路径不对首先应在数据库结构（主要是索引）、服务器当前性能（共享内存、磁盘文件碎片）、数据库对象（表、索引）统计信息是否正确这几方面分析。

心生则种种魔生；心灭则种种魔灭

查看详细资料

TOP

洁净水源 德国世保康直饮水净化器 甘泉热线：0563-3019369，13966197369 维修：13965667864

DamYankee

VIP

老马

UID

476 

精华

38 

积分

17998 

功德

483 金豆 

来自

宣城 

• 个人空间
• 发短消息
• 加为好友
• 当前离线

3^# 大 中 小 发表于 2006-4-24 08:29  只看该作者

1、转换函数
与date操作关系最大的就是两个转换函数：to_date(),to_char()
to_date() 作用将字符类型按一定格式转化为日期类型：
具体用法:to_date(''2004-11-27'',''yyyy-mm-dd''),前者为字符串，后者为转换日期格式，注意，前后两者要以一对应。
如;to_date(''2004-11-27 13:34:43'', ''yyyy-mm-dd hh24:mi:ss'') 将得到具体的时间

多种日期格式：

YYYY：四位表示的年份
YYY，YY，Y：年份的最后三位、两位或一位，缺省为当前世纪
MM：01~12的月份编号
MONTH：九个字符表示的月份，右边用空格填补
MON：三位字符的月份缩写
WW：一年中的星期
D：星期中的第几天
DD：月份中的第几天
DDD：年所中的第几天
DAY：九个字符表示的天的全称，右边用空格补齐
HH，HH12：一天中的第几个小时，12进制表示法
HH24：一天中的第几个小时，取值为00~23
MI：一小时中的分钟
SS：一分钟中的秒
SSSS：从午夜开始过去的秒数

to_char():将日期转按一定格式换成字符类型
SQL> select to_char(sysdate,''yyyy-mm-dd hh24:mi:ss'') time from dual;

TIME
-------------------
2004-10-08 15:22:58

即把当前时间按yyyy-mm-dd hh24:mi:ss格式转换成字符类型

在oracle中处理日期大全

  TO_DATE格式  
Day:  
dd number 12  
dy abbreviated fri  
day spelled out friday  
ddspth spelled out, ordinal twelfth  
Month:  
mm number 03  
mon abbreviated mar  
month spelled out march  
Year:  
yy two digits 98  
yyyy four digits 1998  

24小时格式下时间范围为： 0:00:00 - 23:59:59....  
12小时格式下时间范围为： 1:00:00 - 12:59:59 ....  

[ZT]日期和??函???
1.  
日期和字符转换函数用法（to_date,to_char）  

2.  
select to_char( to_date(222,''J''),''Jsp'') from dual  

显示Two Hundred Twenty-Two  

3.  
求某天是星期几  
select to_char(to_date(''2002-08-26'',''yyyy-mm-dd''),''day'') from dual;  
星期一  
select to_char(to_date(''2002-08-26'',''yyyy-mm-dd''),''day'',''NLS_DATE_LANGUAGE = American'') from dual;  
monday  
设置日期语言  
ALTER SESSION SET NLS_DATE_LANGUAGE=''AMERICAN'';  
也可以这样  
TO_DATE (''2002-08-26'', ''YYYY-mm-dd'', ''NLS_DATE_LANGUAGE = American'')  

4.  
两个日期间的天数  
select floor(sysdate - to_date(''20020405'',''yyyymmdd'')) from dual;  

5. 时间为null的用法  
select id, active_date from table1  
UNION  
select 1, TO_DATE(null) from dual;  

注意要用TO_DATE(null)  

6.  
a_date between to_date(''20011201'',''yyyymmdd'') and to_date(''20011231'',''yyyymmdd'')  
那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。  
所以，当时间需要精确的时候，觉得to_char还是必要的  
7. 日期格式冲突问题  
输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: ''01-Jan-01''  
alter system set NLS_DATE_LANGUAGE = American  
alter session set NLS_DATE_LANGUAGE = American  
或者在to_date中写  
select to_char(to_date(''2002-08-26'',''yyyy-mm-dd''),''day'',''NLS_DATE_LANGUAGE = American'') from dual;  
注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，  
可查看  

select * from nls_session_parameters  
select * from V$NLS_PARAMETERS  

8.  
select count(*)  
from ( select rownum-1 rnum  
from all_objects  
where rownum <= to_date(''2002-02-28'',''yyyy-mm-dd'') - to_date(''2002-  
02-01'',''yyyy-mm-dd'')+1  
)  
where to_char( to_date(''2002-02-01'',''yyyy-mm-dd'')+rnum-1, ''D'' )  
not  
in ( ''1'', ''7'' )  

查找2002-02-28至2002-02-01间除星期一和七的天数  
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).  

9.  
select months_between(to_date(''01-31-1999'',''MM-DD-YYYY''),  
to_date(''12-31-1998'',''MM-DD-YYYY'')) "MONTHS" FROM DUAL;  
1  

select months_between(to_date(''02-01-1999'',''MM-DD-YYYY''),  
to_date(''12-31-1998'',''MM-DD-YYYY'')) "MONTHS" FROM DUAL;  

1.03225806451613  
10. Next_day的用法  
Next_day(date, day)  

Monday-Sunday, for format code DAY  
Mon-Sun, for format code DY  
1-7, for format code D  

11  
select to_char(sysdate,''hh:mi:ss'') TIME from all_objects  
注意：第一条记录的TIME 与最后一行是一样的  
可以建立一个函数来处理这个问题  
create or replace function sys_date return date is  
begin  
return sysdate;  
end;  

select to_char(sys_date,''hh:mi:ss'') from all_objects;  
12.  
获得小时数  

SELECT EXTRACT(HOUR FROM TIMESTAMP ''2001-02-16 2:38:40'') from offer  
SQL> select sysdate ,to_char(sysdate,''hh'') from dual;  

SYSDATE TO_CHAR(SYSDATE,''HH'')  
-------------------- ---------------------  
2003-10-13 19:35:21 07  

SQL> select sysdate ,to_char(sysdate,''hh24'') from dual;  

SYSDATE TO_CHAR(SYSDATE,''HH24'')  
-------------------- -----------------------  
2003-10-13 19:35:21 19  

获取年月日与此类似  
13.  
年月日的处理  
select older_date,  
newer_date,  
years,  
months,  
abs(  
trunc(  
newer_date-  
add_months( older_date,years*12+months )  
)  
) days  
from ( select  
trunc(months_between( newer_date, older_date )/12) YEARS,  
mod(trunc(months_between( newer_date, older_date )),  
12 ) MONTHS,  
newer_date,  
older_date  
from ( select hiredate older_date,  
add_months(hiredate,rownum)+rownum newer_date  
from emp )  
)  

14.  
处理月份天数不定的办法  
select to_char(add_months(last_day(sysdate) +1, -2), ''yyyymmdd''),last_day(sysdate) from dual  

16.  
找出今年的天数  
select add_months(trunc(sysdate,''year''), 12) - trunc(sysdate,''year'') from dual  

闰年的处理方法  
to_char( last_day( to_date(''02'' || :year,''mmyyyy'') ), ''dd'' )  
如果是28就不是闰年  

17.  
yyyy与rrrr的区别  
''YYYY99 TO_C  
------- ----  
yyyy 99 0099  
rrrr 99 1999  
yyyy 01 0001  
rrrr 01 2001  

18.不同时区的处理  
select to_char( NEW_TIME( sysdate, ''GMT'',''EST''), ''dd/mm/yyyy hh:mi:ss'') ,sysdate  
from dual;  

19.  
5秒钟一个间隔  
Select TO_DATE(FLOOR(TO_CHAR(sysdate,''SSSSS'')/300) * 300,''SSSSS'') ,TO_CHAR(sysdate,''SSSSS'')  
from dual  

2002-11-1 9:55:00 35786  
SSSSS表示5位秒数  

20.  
一年的第几天  
select TO_CHAR(SYSDATE,''DDD''),sysdate from dual  
310 2002-11-6 10:03:51  

21.计算小时,分,秒,毫秒  
select  
Days,  
A,  
TRUNC(A*24) Hours,  
TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,  
TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,  
TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds  
from  
(  
select  
trunc(sysdate) Days,  
sysdate - trunc(sysdate) A  
from dual  
)  



select * from tabname  
order by decode(mode,''FIFO'',1,-1)*to_char(rq,''yyyymmddhh24miss'');  

//  
floor((date2-date1) /365) 作为年  
floor((date2-date1, 365) /30) 作为月  
mod(mod(date2-date1, 365), 30)作为日.  
23.next_day函数  
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。  
1 2 3 4 5 6 7  
日 一 二 三 四 五 六

oracle中有很多关于日期的函数

在oracle中有很多关于日期的函数，如：
1、add_months()用于从一个日期值增加或减少一些月份
date_value:=add_months(date_value,number_of_months)
例：
SQL> select add_months(sysdate,12) "Next Year" from dual;

Next Year
----------
13-11月-04

SQL> select add_months(sysdate,112) "Last Year" from dual;

Last Year
----------
13-3月 -13

SQL>

2、current_date()返回当前会放时区中的当前日期
date_value:=current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone,current_date from dual;

SESSIONTIMEZONE CURRENT_DA
--------------- ----------
+08:00 13-11月-03

SQL> alter session set time_zone=''-11:00''
  2 /

会话已更改。

SQL> select sessiontimezone,current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11月-03 04.59.13.668000 下午 -11:
                00

SQL>

3、current_timestamp()以timestamp with time zone数据类型返回当前会放时区中的当前日期
timestamp_with_time_zone_value:=current_timestamp([timestamp_precision])
SQL> column sessiontimezone for a15
SQL> column current_timestamp format a36
SQL> select sessiontimezone,current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
+08:00 13-11月-03 11.56.28.160000 上午 +08:
                00

SQL> alter session set time_zone=''-11:00''
  2 /

会话已更改。

SQL> select sessiontimezone,current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11月-03 04.58.00.243000 下午 -11:
                00

SQL>

4、dbtimezone()返回时区
varchar_value:=dbtimezone
SQL> select dbtimezone from dual;

DBTIME
------
-07:00

SQL>

5、extract()找出日期或间隔值的字段值
date_value:=extract(date_field from [datetime_value|interval_value])
SQL> select extract(month from sysdate) "This Month" from dual;

This Month
----------
        11

SQL> select extract(year from add_months(sysdate,36)) "3 Years Out" from dual;

3 Years Out
-----------
       2006

SQL>

6、last_day()返回包含了日期参数的月份的最后一天的日期
date_value:=last_day(date_value)
SQL> select last_day(date''2000-02-01'') "Leap Yr?" from dual;

Leap Yr?
----------
29-2月 -00

SQL> select last_day(sysdate) "Last day of this month" from dual;

Last day o
----------
30-11月-03

SQL>

7、localtimestamp()返回会话中的日期和时间
timestamp_value:=localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual;

LOCALTIMESTAMP
----------------------------
13-11月-03 12.09.15.433000
下午

SQL> select localtimestamp,current_timestamp from dual;

LOCALTIMESTAMP CURRENT_TIMESTAMP
---------------------------- ------------------------------------
13-11月-03 12.09.31.006000 13-11月-03 12.09.31.006000 下午 +08:
下午 00

SQL> alter session set time_zone=''-11:00'';

会话已更改。

SQL> select localtimestamp,to_char(sysdate,''DD-MM-YYYY HH:MI:SS AM'') "SYSDATE" from dual;

LOCALTIMESTAMP SYSDATE
---------------------------- ------------------------
12-11月-03 05.11.31.259000 13-11-2003 12:11:31 下午
下午

SQL>

8、months_between()判断两个日期之间的月份数量
number_value:=months_between(date_value,date_value)
SQL> select months_between(sysdate,date''1971-05-18'') from dual;

MONTHS_BETWEEN(SYSDATE,DATE''1971-05-18'')
----------------------------------------
                              389.855143

SQL> select months_between(sysdate,date''2001-01-01'') from dual;

MONTHS_BETWEEN(SYSDATE,DATE''2001-01-01'')
----------------------------------------
                              34.4035409

SQL>

9、next_day()给定一个日期值，返回由第二个参数指出的日子第一次出现在的日期值（应返回相应日子的名称字符串）

?周相?日期函?

1.查询某周的第一天
select trunc(decode(ww, 53, to_date(yy || ''3112'', ''yyyyddmm''), to_date(yy || ''-'' || to_char(ww * 7), ''yyyy-ddd'')), ''d'') last_day
from (select substr(''2004-32'', 1, 4) yy, to_number(substr(''2004-32'', 6)) ww
         from dual)

select trunc(to_date(substr(''2003-01'',1,5)||to_char((to_number(substr(''2003-01'',6)))*7),''yyyy-ddd''),''d'')-6 first_day from dual

select min(v_date) from
  (select (to_date(''200201'',''yyyymm'') + rownum) v_date
  from all_tables
  where rownum < 370)
where to_char(v_date,''yyyy-iw'') = ''2002-49''

2.查询某周的最后一天

复制内容到剪贴板

代码:

select trunc(decode(ww, 53, to_date(yy || ''3112'', ''yyyyddmm''), to_date(yy || ''-'' || to_char(ww * 7), ''yyyy-ddd'')), ''d'') - 6 first_day
  from (select substr(''2004-33'', 1, 4) yy, to_number(substr(''2004-33'', 6)) ww
          from dual)
         
select trunc(to_date(substr(''2003-01'',1,5)||to_char((to_number(substr(''2003-01'',6)))*7),''yyyy-ddd''),''d'') last_day from dual

select max(v_date) from
  (select (to_date(''200408'',''yyyymm'') + rownum) v_date
  from all_tables
  where rownum < 370)
where to_char(v_date,''yyyy-iw'') = ''2004-33''

3.查询某周的日期
select min_date, to_char(min_date,''day'') day from
(select to_date(substr(''2004-33'',1,4)||''001''+rownum-1,''yyyyddd'') min_date
        from all_tables
  where rownum <= decode(mod(to_number(substr(''2004-33'',1,4)),4),0,366,365)  
  union

  select to_date(substr(''2004-33'',1,4)-1||
         decode(mod(to_number(substr(''2004-33'',1,4))-1,4),0,359,358)+rownum,''yyyyddd'') min_date
        from all_tables         
          where rownum <= 7
  union

  select to_date(substr(''2004-33'',1,4)+1||''001''+rownum-1,''yyyyddd'') min_date
        from all_tables         
          where rownum <= 7                       
)
where to_char(min_date,''yyyy-iw'') =''2004-33''

心生则种种魔生；心灭则种种魔灭

 

TOP

买房、卖房，请找“房舟房产”，电话：2299977，地址：锦城南路106号（建行隔壁）

DamYankee

VIP

老马

UID

476 

精华

38 

积分

17998 

功德

483 金豆 

来自

宣城 

• 个人空间
• 发短消息
• 加为好友
• 当前离线

4^# 大 中 小 发表于 2006-4-24 08:38  只看该作者

　　DATE数据类型
　　
　　这个数据类型我们实在是太熟悉了，当我们需要表示日期和时间的话都会想到date类型。它可以存储月，年，日，世纪，时，分和秒。它典型地用来表示什么时候事情已经发生或将要发生。DATE数据类型的问题在于它表示两个事件发生时间间隔的度量粒度是秒。这个问题将在文章稍后讨论timestamp的时候被解决。可以使用TO_CHAR函数把DATE数据进行传统地包装，达到表示成多种格式的目的。
　　

复制内容到剪贴板

代码:

　　SQL> SELECT TO_CHAR(date1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table;
　　
　　Date

　　
　　---------------------------
　　
　　06/20/2003 16:55:14
　　
　　06/26/2003 11:16:36
　　
　　我见到的大多数人陷入的麻烦就是计算两个时间间的间隔年数、月数、天数、小时数和秒数。你需要明白的是，当你进行两个日期的相减运算的时候，得到的是天数。你需要乘上每天的秒数（1天=86400秒），然后，你可以再次计算得到你想要的间隔数。下面就是我的解决方法，可以精确计算出两个时间的间隔。我明白这个例子可以更简短些，但是我是为了显示所有的数字来强调计算方式。
　　

复制内容到剪贴板

代码:

　　1　　　　 SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,
　　
　　　2　　　　 TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2,
　　
　　　3　　　　 trunc(86400*(date2-date1))-
　　
　　　4　　　　 60*(trunc((86400*(date2-date1))/60)) seconds,
　　
　　　5　　　　 trunc((86400*(date2-date1))/60)-
　　
　　　6　　　　 60*(trunc(((86400*(date2-date1))/60)/60)) minutes,
　　
　　　7　　　　 trunc(((86400*(date2-date1))/60)/60)-
　　
　　　8　　　　 24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours,
　　
　　　9　　　　 trunc((((86400*(date2-date1))/60)/60)/24) days,
　　
　　10　　　　 trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks
　　
　　11*　　　　FROM date_table

　
　
　　DATE1　　　　　　 DATE2　　　　　　　　SECONDS　　MINUTES　　　HOURS　　　DAYS　　　WEEKS
　　
　　----------------- ----------------- ---------- ---------- ---------- ---------- ----------
　　
　　06202003:16:55:14 07082003:11:22:57　　　　 43　　　　 27　　　　 18　　　　17　　　　　2
　　
　　06262003:11:16:36 07082003:11:22:57　　　　 21　　　　　6　　　　　0　　　　12　　　　　1
　　
　　TIMESTAMP 数据类型
　　
　　DATE数据类型的主要问题是它粒度不能足够区别出两个事件哪个先发生。ORACLE已经在DATE数据类型上扩展出来了TIMESTAMP数据类型，它包括了所有DATE数据类型的年月日时分秒的信息，而且包括了小数秒的信息。如果你想把DATE类型转换成TIMESTAMP类型，就使用CAST函数。
　　

复制内容到剪贴板

代码:

　　SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t;

　　
　　Date
　　
　　-----------------------------------------------------
　　
　　20-JUN-03 04.55.14.000000 PM
　　
　　26-JUN-03 11.16.36.000000 AM
　　
　　正如你看到的，在转换后的时间段尾部有了一段“.000000”。这是因为从date转换过来的时候，没有小数秒的信息，缺省为0。而且显示格式是按照参数NLS_TIMESTAMP_FORMAT定的缺省格式显示。当你把一个表中date类型字段的数据移到另一个表的timestamp类型字段中去的时候，可以直接写INSERT SELECT语句，oracle会自动为你做转换的。
　　

复制内容到剪贴板

代码:

　　1　SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table

　　
　　Date
　　
　　-------------------
　　
　　06/20/2003 16:55:14
　　
　　06/26/2003 11:16:36