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

Oracle中與時間有關的計算

2013-11-13 16:14:50  來源: Oracle 

  Oracle中與時間有關的計算

  [sql]

  # sysdate+/        小時

  # sysdate+//     分鐘

  # sysdate+///  秒鐘

  #

  SQL> alter session set nls_date_format=yyyyMMdd hh:mi:ss;

  Session altered

  SQL> select sysdate sysdate+/ sysdate+// sysdate+/// from dual;

  SYSDATE             SYSDATE+/        SYSDATE+//     SYSDATE+///

  

   :: :: :: ::

  SQL> 獲取本周第一天

  SQL> select trunc(sysdate day) from dual;

  TRUNC(SYSDATEDAY

  

   ::

  SQL> 獲取本周最後一天

  SQL> select trunc(sysdate day) + /// from dual;

  TRUNC(SYSDATEDAY

  

   ::

  SQL> 獲取下周第 n 天

  SQL> select trunc(next_day(sysdate )) trunc(next_day(sysdate )) from dual;

  TRUNC(NEXT_DAY(SYSD TRUNC(NEXT_DAY(SYSD

  

   :: ::

  SQL>

  SQL> 獲取上月第一天

  SQL> select trunc(add_months(sysdate month) from dual;

  TRUNC(ADD_MONTHS(SY

  

   ::

  SQL> select to_date(to_char(add_months(sysdate yyyyMM yyyyMM) from dual;

  TO_DATE(TO_CHAR(ADD

  

   ::

  SQL> 獲取本月第一天

  SQL> select trunc(sysdate month) from dual;

  TRUNC(SYSDATEMONT

  

   ::

  SQL> select to_date(to_char(sysdate yyyyMM yyyyMM) from dual;

  TO_DATE(TO_CHAR(SYS

  

   ::

  SQL> 獲取本月最後一天

  SQL> select trunc(last_day(sysdate)) + /// from dual;

  TRUNC(LAST_DAY(SYSD

  

   ::

  SQL> select add_months(trunc(sysdate month /// from dual;

  ADD_MONTHS(TRUNC(SY

  

   ::

  SQL>

  SQL> 獲取本季度第一天

  SQL> select trunc(sysdate Q) from dual;

  TRUNC(SYSDATEQ

  

   ::

  SQL> 獲取本季度最後一天

  SQL> select add_months(trunc(sysdate Q /// from dual;

  ADD_MONTHS(TRUNC(SY

  

   ::

  SQL>

  SQL> 獲取去年第一天(因為閏年平年天數不一樣所以不能使用加減 天 來計算年份)

  SQL> select trunc(trunc(sysdate year year) from dual;

  TRUNC(TRUNC(SYSDATE

  

   ::

  SQL> 獲取今年第一天

  SQL> select trunc(sysdate year) from dual;

  TRUNC(SYSDATEYEAR

  

   ::

  SQL> select to_date(to_char(sysdate yyyy)|| yyyyMMdd) from dual;

  TO_DATE(TO_CHAR(SYS

  

   ::

  SQL> 獲取今年最後一天

  SQL> select trunc(add_months(sysdate year /// from dual;

  TRUNC(ADD_MONTHS(SY

  

   ::

  SQL> select to_date(to_char(sysdate yyyy)|| :: yyyyMMdd hh:mi:ss) from dual;

  TO_DATE(TO_CHAR(SYS

  

   ::

  SQL>

  SQL> 計算相差的天數

  SQL> select round(to_number(to_date( :: yyyyMMdd hh:mi:ss to_date( :: yyyyMMdd hh:mi:ss))) value from dual;

  VALUE

  

  

  SQL> 計算相差的小時數

  SQL> select round(to_number(to_date( :: yyyyMMdd hh:mi:ss to_date( :: yyyyMMdd hh:mi:ss)) * ) value from dual;

  VALUE

  

  

  SQL> 計算相差的分鐘數

  SQL> select round(to_number(to_date( :: yyyyMMdd hh:mi:ss to_date( :: yyyyMMdd hh:mi:ss)) * * ) value from dual;

  VALUE

  

  

  SQL>


From:http://tw.wingwit.com/Article/program/Oracle/201311/18017.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.