详解oracle 日期格式（总结）

select to_char(sysdate, 'yy-mm-dd hh24:mi:ss' ) from dual  //显示：08-11-07 13:22:42

select to_date( '2005-12-25,13:25:59' , 'yyyy-mm-dd,hh24:mi:ss' ) from dual //显示：2005-12-25 13:25:59

select sysdate,sysdate - interval '7' MINUTE from dual

select sysdate - interval '7' hour from dual

select sysdate - interval '7' day from dual

select sysdate,sysdate - interval '7' month from dual

select sysdate,sysdate - interval '7' year from dual

select sysdate,sysdate - 8*interval '7' hour from dual

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

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

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;

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

alter system set NLS_DATE_LANGUAGE = American  

alter session set NLS_DATE_LANGUAGE = American

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

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

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

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

Next_day( date , day )

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

create or replace function sys_date return date is   

begin   

return sysdate;  

end ;  

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

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  

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

'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

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

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)作为日.

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

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

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 //时间差-秒

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;