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 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