转贴：oracle日期处理完全版
TO_DATE格式
复制内容到剪贴板
24小时格式下时间范围为： 0:00:00 - 23:59:59....
代码:Day:
12小时格式下时间范围为： 1:00:00 - 12:59:59 .... 1. 日期和字符转换函数用法（to_date,to_char） 2.
复制内容到剪贴板
显示Two Hundred Twenty-Two
代码:select to_char( to_date(222,'J'),'Jsp') from dual
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;
复制内容到剪贴板
也可以这样
代码:ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
复制内容到剪贴板
4.
代码:TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
两个日期间的天数
复制内容到剪贴板
5. 时间为null的用法
代码:select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
复制内容到剪贴板
注意要用TO_DATE(null)
代码:select id, active_date from table1
6.
复制内容到剪贴板
那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
代码:a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
所以，当时间需要精确的时候，觉得to_char还是必要的 7. 日期格式冲突问题 输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'
复制内容到剪贴板
或者在to_date中写
代码:alter system set NLS_DATE_LANGUAGE = American
复制内容到剪贴板
注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，
代码:select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
可查看
复制内容到剪贴板
8.
代码:select * from nls_session_parameters
复制内容到剪贴板
查找2002-02-28至2002-02-01间除星期一和七的天数
代码:select count(*)
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒). 9.
复制内容到剪贴板
1
代码:select months_between(to_date('01-31-1999','MM-DD-YYYY'),
复制内容到剪贴板
1.03225806451613
代码:select months_between(to_date('02-01-1999','MM-DD-YYYY'),
10. Next_day的用法
复制内容到剪贴板
11
代码:Next_day(date, day)
复制内容到剪贴板
注意：第一条记录的TIME 与最后一行是一样的
代码:select to_char(sysdate,'hh:mi:ss') TIME from all_objects
可以建立一个函数来处理这个问题
复制内容到剪贴板
12.
代码:create or replace function sys_date return date is
获得小时数
复制内容到剪贴板
SYSDATE TO_CHAR(SYSDATE,'HH')
代码:SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer
-------------------- --------------------- 2003-10-13 19:35:21 07
复制内容到剪贴板
SYSDATE TO_CHAR(SYSDATE,'HH24')
代码:SQL> select sysdate ,to_char(sysdate,'hh24') from dual;
-------------------- ----------------------- 2003-10-13 19:35:21 19 获取年月日与此类似 13. 年月日的处理
复制内容到剪贴板
14.
代码:select older_date,
处理月份天数不定的办法
复制内容到剪贴板
16.
代码:select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual
找出今年的天数
复制内容到剪贴板
闰年的处理方法
代码:select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
复制内容到剪贴板
如果是28就不是闰年
代码:to_char( last_day( to_date('02' || :year,'mmyyyy') ), 'dd' )
17. yyyy与rrrr的区别 'YYYY99 TO_C ------- ---- yyyy 99 0099 rrrr 99 1999 yyyy 01 0001 rrrr 01 2001 18.不同时区的处理
复制内容到剪贴板
19.
代码:select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate
5秒钟一个间隔
复制内容到剪贴板
2002-11-1 9:55:00 35786
代码:Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')
SSSSS表示5位秒数 20. 一年的第几天
复制内容到剪贴板
21.计算小时,分,秒,毫秒
代码:select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
复制内容到剪贴板
//
代码:select
floor((date2-date1) /365) 作为年 floor((date2-date1, 365) /30) 作为月 mod(mod(date2-date1, 365), 30)作为日. 23.next_day函数 next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
心生则种种魔生；心灭则种种魔灭
| |
DamYankee
VIP
老马
|
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执行分析方面已经比较成熟，如果分析执行的路径不对首先应在数据库结构（主要是索引）、服务器当前性能（共享内存、磁盘文件碎片）、数据库对象（表、索引）统计信息是否正确这几方面分析。
心生则种种魔生；心灭则种种魔灭
|
| DamYankee
VIP
老马
|
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
心生则种种魔生；心灭则种种魔灭
|
| DamYankee
VIP
老马
|
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;
--------------------------- 06/20/2003 16:55:14 06/26/2003 11:16:36 我见到的大多数人陷入的麻烦就是计算两个时间间的间隔年数、月数、天数、小时数和秒数。你需要明白的是，当你进行两个日期的相减运算的时候，得到的是天数。你需要乘上每天的秒数（1天=86400秒），然后，你可以再次计算得到你想要的间隔数。下面就是我的解决方法，可以精确计算出两个时间的间隔。我明白这个例子可以更简短些，但是我是为了显示所有的数字来强调计算方式。
复制内容到剪贴板
代码: 1 SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,
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 |
扫一扫
555
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
