如何向 Oracle 表中的 Date 欄位插入日期及時間____Oracle

工作中要用到 Oracle 9i，經常要向其中的某張表插入事件發生的日期及時間。專門就 Oracle 的日期及時間顯示方式和插入方式記一筆。

像 Number，varchar2 等內建的資料類型一樣，Oracle 用 Date 這個內建的資料類型來儲存日期和時間。和 MS SQL Server 一樣，日期和時間是儲存在一個資料類型裡的，沒有只儲存時間或只儲存日期的單獨的時間和日期資料類型。DATE 資料類型儲存年月日和時分秒。

當顯示 DATE 類型的資料時，Oracle 先要把儲存的值從內部儲存格式轉化為可輸出的字串。通常，這種轉換是通過 TO_CHAR 這個函數來完成的。如果沒有用 TO_CHAR 函數來指定特定的日期時間格式，Oracle 預設的顯示格式是“DD-MON-YY”。 例如，我們建立了一個表 x：

create table x(a int, b date);

然後我們執行下面的查詢語句：

select b from x; 

我們會看到如下結果(set head on):

B------------ 01-APR-08 (語言是英語) 或 B------------- 01-四月 -08 (語言是中文)

也就是說，每次顯示 DATE 類型的數值時，Oracle 會自動調用 TO_CHAR 函數，並以預設日期顯示格式為參數。我們可以顯式地調用 TO_CHAR 函數，並指定自己想要的格式。例如：

SELECT TO_CHAR(b, 'YYYY/MM/DD') AS b FROM x;

返回的結果是：

B------------ 2008/04/01

TO_CHAR 是個很強大的函數，可以把 Date，MLSLABEL，Number 類型的資料按指定的格式轉換為 VARCHAR2 類型的資料，我們在這裡只關心日期時間的轉換。文法：

TO_CHAR(d [, fmt [, 'nlsparams'] ])

d 是 Date 類型的變數，fmt 是我們指定的日期時間格式，如果不顯式指定就用 Oracle 的預設值。 fmt 裡常用的跟日期時間有關的預留位置如下： MM 用數字表示的月份(例如，07) MON 縮寫的月份名稱(例如，JUL) MONTH 完整的月份名稱(例如，JULY) DD 日期(例如, 24) DY 星期幾的縮寫(例如，FRI) YYYY 用4位表示的年份(例如, 2008) YY 用2位表示的年份，取年份的後兩位(例如，08) RR 跟 YY 類似，但兩位表示的年份被近似到 1950 到 2049 這個範圍裡的年份，例如 06 被認為是 2006,而不是 1906 AM (或 PM) 上下午指示符 HH 12進位表示的時間(1-12) HH24 24進位表示的時間(0-23) MI 分鐘(0-59) SS 秒(0-59)

以上講的是利用 TO_CHAR 函數來顯示 DATE 類型的資料，下面講講如何向表裡插入一個 Date 類型的數值。這時我們要用到 TO_DATE 這個函數，把字串表示的日期轉換為 Date 類型。

跟 Oracle 顯示日期時間時會隱性調用 TO_CHAR 函數一樣，當 Oracle 期望一個 Date 類型的值時，它會隱性調用 TO_DATE 函數來轉換輸入的字串，依據的預設格式是“DD-MON-YY”。

還是以我們的 x 表為例，我們可以直接輸入：

insert into x values(99, '31-may-08');

但是，在顯示時不管用什麼格式都不會影響我們實際儲存的資料；在插入時，單純採用 Oracle 的預設格式就不是那麼符合工作需要了，我們還是需要顯式調用 TO_DATE 函數，例如：

insert into x values(99, to_date('2008/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));

TO_DATE 函數的文法：

TO_DATE(char [, fmt [, 'nlsparams'] ])

char 是表示日期和時間的字串。fmt 的表示方法和 TO_CHAR 函數一樣。

我們前面一直提到 Oracle 預設的日期時間格式是“DD-MON-YY”，其實，我們還可以修改這個預設格式，把它改成我們需要的格式。在 SQL*plus 裡面輸入下面的命令：

alter session set NLS_DATE_FORMAT='<my_format>';   ——這個改變只對當前的會話(session)有用。

例如：

SQL> alter   session   set   nls_date_format='yyyy-mm-dd';會話已更改。SQL> insert  into  x (b)  values('2004-08-26');已建立1行。

用 Oracle 內建的函數 SYSDATE 可以返回系統當前的日期和時間，例如：

select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time" from dual;

Two interesting things to note here:

You can use double quotes to make names case sensitive (by default, SQL is case insensitive), or to force spaces into names. Oracle will treat everything inside the double quotes literally as a single name. In this example, if "Current Time" is not quoted, it would have been interpreted as two case insensitive names CURRENT and TIME, which would actually cause a syntax error. DUAL is built-in relation in Oracle which serves as a dummy relation to put in the FROM clause when nothing else is appropriate. For example, try "select 1+2 from dual;". Another name for the built-in function SYSDATE is CURRENT_DATE. Be aware of these special names to avoid name conflicts. Operations on DATE You can compare DATE values using the standard comparison operators such as =, !=, >, etc.

 

You can subtract two DATE values, and the result is a FLOAT which is the number of days between the two DATE values. In general, the result may contain a fraction because DATE also has a time component. For obvious reasons, adding, multiplying, and dividing two DATE values are not allowed.

You can add and subtract constants to and from a DATE value, and these numbers will be interpreted as numbers of days. For example, SYSDATE+1 will be tomorrow. You cannot multiply or divide DATE values.

With the help of TO_CHAR, string operations can be used on DATE values as well. For example, to_char(<date>, 'DD-MON-YY') like '%JUN%' evaluates to true if <date> is in June.