Oracle之TO_DATE用法详解

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss' ) as nowTime from dual;   //日期转化为字符串 

select to_char(sysdate, 'yyyy' )  as nowYear   from dual;   //获取时间的年 

select to_char(sysdate, 'mm' )    as nowMonth  from dual;   //获取时间的月 

select to_char(sysdate, 'dd' )    as nowDay    from dual;   //获取时间的日 

select to_char(sysdate, 'hh24' )  as nowHour   from dual;   //获取时间的时 

select to_char(sysdate, 'mi' )    as nowMinute from dual;   //获取时间的分 

select to_char(sysdate, 'ss' )    as nowSecond from dual;   //获取时间的秒

select to_date( '2004-05-07 13:23:44' , 'yyyy-mm-dd hh24:mi:ss' ) from dual

select to_char( to_date(222, 'J' ), 'Jsp' ) from dual //显示Two Hundred Twenty-Two

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

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

select id, active_date from table1    

UNION     

select 1, TO_DATE( null ) from dual;  //注意要用TO_DATE( null )

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

//那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。    

//所以，当时间需要精确的时候，觉得to_char还是必要的

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

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秒, 而不是毫秒).

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

Next_day( date , day )    

Monday-Sunday, for format code DAY     

Mon-Sun, for format code DY    

1-7, for format code D

//extract()找出日期或间隔值的字段值

SELECT EXTRACT( HOUR FROM TIMESTAMP '2001-02-16 2:38:40' ) from offer    

select sysdate ,to_char(sysdate, 'hh' ) from dual;    

SYSDATE               TO_CHAR(SYSDATE, 'HH' )    

-------------------- ---------------------    

2003-10-13 19:35:21   07    

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

SYSDATE               TO_CHAR(SYSDATE, 'HH24' )    

-------------------- -----------------------    

2003-10-13 19:35:21   19

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

       )

   )

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   

    //闰年的处理方法    

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

    //如果是28就不是闰年

YYYY99  TO_C    

------- ----    

yyyy 99 0099    

rrrr 99 1999    

yyyy 01 0001    

rrrr 01 2001

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

   sysdate   from dual;

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位秒数   

select TO_CHAR(SYSDATE, 'DDD' ),sysdate from dual  

//310  2002-11-6 10:03:51   

   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) 作为月    

//  d(mod(date2-date1, 365), 30)作为日.

//返回下个星期的日期, day 为1-7或星期日-星期六,1表示星期日

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

// 1  2  3  4  5  6  7    

//日 一 二 三 四 五 六  

select (sysdate-to_date( '2003-12-03 12:55:45' , 'yyyy-mm-dd hh24:mi:ss' ))*24*60*60 from dual

//日期 返回的是天 然后 转换为ss

select sysdate S1,

round(sysdate) S2 ,

round(sysdate, 'year' ) YEAR ,

round(sysdate, 'month' ) MONTH ,

round(sysdate, 'day' ) DAY from dual

select sysdate S1,                   

   trunc(sysdate) S2,                 //返回当前日期,无时分秒

   trunc(sysdate, 'year' ) YEAR ,        //返回当前年的1月1日,无时分秒

   trunc(sysdate, 'month' ) MONTH ,     //返回当前月的1日,无时分秒

   trunc(sysdate, 'day' ) DAY            //返回当前星期的星期天,无时分秒

from dual

select greatest( '01-1月-04' , '04-1月-04' , '10-2月-04' ) from dual<br>

注:oracle时间差是以天数为单位,所以换算成年月,日

  select floor(to_number(sysdate-to_date( '2007-11-02 15:55:03' ,

  'yyyy-mm-dd hh24:mi:ss' ))/365) as spanYears from dual        //时间差-年

  select ceil(moths_between(sysdate-to_date( '2007-11-02 15:55:03' ,

  'yyyy-mm-dd hh24:mi:ss' ))) as spanMonths from dual           //时间差-月

  select floor(to_number(sysdate-to_date( '2007-11-02 15:55:03' ,

  'yyyy-mm-dd hh24:mi:ss' ))) as spanDays from dual             //时间差-天

  select floor(to_number(sysdate-to_date( '2007-11-02 15:55:03' ,

  'yyyy-mm-dd hh24:mi:ss' ))*24) as spanHours from dual         //时间差-时

  select floor(to_number(sysdate-to_date( '2007-11-02 15:55:03' ,

  'yyyy-mm-dd hh24:mi:ss' ))*24*60) as spanMinutes from dual    //时间差-分

  select floor(to_number(sysdate-to_date( '2007-11-02 15:55:03' ,

  'yyyy-mm-dd hh24:mi:ss' ))*24*60*60) as spanSeconds from dual //时间差-秒

//oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss' ),

    to_char(sysdate+n*365, 'yyyy-mm-dd hh24:mi:ss' ) as newTime from dual        //改变时间-年

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss' ),

     add_months(sysdate,n) as newTime from dual                                 //改变时间-月

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss' ),

     to_char(sysdate+n, 'yyyy-mm-dd hh24:mi:ss' ) as newTime from dual            //改变时间-日

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss' ),

     to_char(sysdate+n/24, 'yyyy-mm-dd hh24:mi:ss' ) as newTime from dual         //改变时间-时

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss' ),

     to_char(sysdate+n/24/60, 'yyyy-mm-dd hh24:mi:ss' ) as newTime from dual      //改变时间-分

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss' ),

     to_char(sysdate+n/24/60/60, 'yyyy-mm-dd hh24:mi:ss' ) as newTime from dual   //改变时间-秒

   SELECT Trunc(Trunc(SYSDATE, 'MONTH' ) - 1, 'MONTH' ) First_Day_Last_Month,

     Trunc(SYSDATE, 'MONTH' ) - 1 / 86400 Last_Day_Last_Month,

     Trunc(SYSDATE, 'MONTH' ) First_Day_Cur_Month,

     LAST_DAY(Trunc(SYSDATE, 'MONTH' )) + 1 - 1 / 86400 Last_Day_Cur_Month

FROM dual;