熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

在Oracle中實現各種日期處理完全版

2013-11-13 16:03:33  來源: Oracle 

  TO_DATE格式
    Day:   
    dd    number    
    dy    abbreviated  fri     
    day   spelled out  friday            
    ddspth  spelled out  ordinal  twelfth
    Month:  
    mm    number    
    mon   abbreviated  mar
    month  spelled out  march 
    Year:             
    yy    two digits  
    yyyy   four digits    
    
    小時格式下時間范圍為 :: ::
    小時格式下時間范圍為 :: ::   
  
    日期和字符轉換函數用法(to_dateto_char)   
   
  
    select to_char( to_date(J)Jsp) from dual
    
    顯示Two Hundred TwentyTwo
   
  
    求某天是星期幾
    select to_char(to_date(yyyymmdd)day) from dual; 
    星期一
    select to_char(to_date(yyyymmdd)dayNLS_DATE_LANGUAGE = American) from dual; 
    monday  
    設置日期語言
    ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
    也可以這樣
    TO_DATE ( YYYYmmdd NLS_DATE_LANGUAGE = American)
   
  
    兩個日期間的天數
    select floor(sysdate to_date(yyyymmdd)) from dual;
   
     時間為null的用法
    select id active_date from table
     UNION
    select TO_DATE(null) from dual;
    
    注意要用TO_DATE(null)
   
   
    a_date between to_date(yyyymmdd) and to_date(yyyymmdd)
    那麼號中午點之後和號的點之前是不包含在這個范圍之內的
    所以當時間需要精確的時候覺得to_char還是必要的
     日期格式沖突問題
       輸入的格式要看你安裝的ORACLE字符集的類型 比如: USASCII date格式的類型就是: Jan
    alter system set NLS_DATE_LANGUAGE = American
       alter session set NLS_DATE_LANGUAGE = American
    或者在to_date中寫
    select to_char(to_date(yyyymmdd)dayNLS_DATE_LANGUAGE = American) from dual; 
    注意我這只是舉了NLS_DATE_LANGUAGE當然還有很多
    可查看
    select * from nls_session_parameters
    select * from V$NLS_PARAMETERS
   
  
   日期和字符轉換函數用法(to_dateto_char)
       We overwrite NLS_DATE_FORMAT into different formats for the session 
       SQL> alter session set nls_date_format = DDMONRR; Session altered
       SQL> set pagesize
     SQL> set linesize
     SQL> select * from nls_session_parameters;
     NLS_LANGUAGE          AMERICAN
     NLS_TERRITORY         AMERICA
     NLS_CURRENCY          $
     NLS_ISO_CURRENCY        AMERICA
     NLS_NUMERIC_CHARACTERS    
     NLS_CALENDAR          GREGORIAN
     NLS_DATE_FORMAT        DDMONRR
     NLS_DATE_LANGUAGE       AMERICAN
     NLS_SORT            BINARY
     NLS_TIME_FORMAT        HHMISSXFF AM
     NLS_TIMESTAMP_FORMAT      DDMONRR HHMISSXFF AM
     NLS_TIME_TZ_FORMAT       HHMISSXFF AM TZH:TZM
     NLS_TIMESTAMP_TZ_FORMAT    DDMONRR HHMISSXFF AM TZH:TZM
     NLS_DUAL_CURRENCY       $
     NLS_COMP            BINARY
  
     rows selected
       specify it in SQL statement:
       
      SQL> select to_date(SEPDDMONYYYY) from dual;
       SEP
      SQL> alter session set nls_date_format = MMDDYYYY;
         Session altered
        
      SQL> select to_date(SEPDDMONYY) from dual;
         TO_DATE(
        
          
      SQL> alter session set nls_date_format = RRRRMMDD;
         Session altered 
        
      SQL> select to_date(SEPDDMONYYYY) from dual;
         TO_DATE(
        
          
        
    When we use TO_CHAR function we get expected results of format from current SQL statement: 
      SQL> alter session set nls_date_format = MMDDYY;
         Session altered 
      SQL> select to_char(sysdateddmmyyyy) from dual;
         TO_CHAR(SYSDATEDDMMYYYY) 
          
      SQL> alter session set nls_date_format = RRMONDD;
         Session altered 
      SQL> select to_char(sysdateddmonyy) from dual;
         TO_CHAR(SYSDATEDDMONYY) 
         sep 
      SQL> select to_char(sysdateddMonyy) from dual;
         TO_CHAR(SYSDATEDDMONYY) 
         Sep
  
    select count(*) 
    from ( select rownum rnum 
       from all_objects 
       where rownum <= to_date(yyyymmdd) to_date(
       yyyymmdd)+ 
       ) 
    where to_char( to_date(yyyymmdd)+rnum D )
    not 
    in ( )
    
    查找間除星期一和七的天數
    在前後分別調用DBMS_UTILITYGET_TIME 讓後將結果相減(得到的是/ 而不是毫秒)  
   
  
    select months_between(to_date(MMDDYYYY) 
     to_date(MMDDYYYY)) MONTHS FROM DUAL; 
   
    
    select months_between(to_date(MMDDYYYY)  
     to_date(MMDDYYYY)) MONTHS FROM DUAL; 
    
   
     Next_day的用法
    Next_day(date day)
    
    MondaySunday for format code DAY   
    MonSun for format code DY     
    for format code D 
   
     
    select to_char(sysdatehh:mi:ss) TIME from all_objects
    注意第一條記錄的TIME 與最後一行是一樣的
    可以建立一個函數來處理這個問題
    create or replace function sys_date return date is 
    begin 
    return sysdate; 
    end; 
    
    select to_char(sys_datehh:mi:ss) from all_objects; 
  
       獲得小時數
       
       SELECT EXTRACT(HOUR FROM TIMESTAMP ::) from offer
       SQL>  select sysdate to_char(sysdatehh) from dual;
       
       SYSDATE       TO_CHAR(SYSDATEHH)
      
       :: 
       
       SQL>  select sysdate to_char(sysdatehh) from dual;
       
       SYSDATE       TO_CHAR(SYSDATEHH)
      
       :: 
       
       獲取年月日與此類似
  
    年月日的處理
    select older_date
        newer_date
        years
        months
        abs(
         trunc(
           newer_date
             add_months( older_dateyears*+months )
            )
         ) days
     from ( select 
       trunc(months_between( newer_date older_date )/) YEARS
      mod(tr
From:http://tw.wingwit.com/Article/program/Oracle/201311/17738.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.