在PL/SQL中使用日期类型(2)

如何修改？【译者注：以下摘自网络】
1）.可以在用户环境变量中指定（LINUX)。 在用户的.bash_profile中增加两句：
export NLS_LANG=AMERICAN —要注意这一句必须指定，不然下一句不生效。
export NLS_DATE_FORMAT=’yyyy/mm/dd hh24:mi:ss’
2）.在SQLPLUS的glogin.sql文件增加一句： alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
3）.直接修改当前会话的日期格式 ： alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
4）.修改数据库的参数，需要重启数据库后生效 SQL> alter system set nls_date_format=’yyyy-mm-dd hh24:mi:ss’ scope=spfile;

4、实现日期到字符串和字符串到日期的转换？

正如to_char函数对于数字一样，我们使用另外一个版本的to_char实现日期或时间戳类型到字符串的转换。

如果使用了不带格式参数的to_char。则数据库使用隐式转换。

BEGIN DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE)); DBMS_OUTPUT.put_line ( TO_CHAR (SYSTIMESTAMP)); END; / 07-AUG-11 07-AUG-11 08.55.00.470000000 AM -05:00

Use TO_CHAR to display the full names of both the day and the month in the date:

BEGIN DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, 'Day, DDth Month YYYY')); END; / Sunday , 07TH August 2011

Note: The language used to display these names is determined by the NLS_DATE_LANGUAGE setting, which can also be specified as the third argument in the call to TO_CHAR, as in
注意：用语显示的日期语言由参数：NLS_DATE_LANGUAGE决定。其可以作为to_char的第3个参数使用：
如下：

BEGIN DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, 'Day, DDth Month YYYY', 'NLS_DATE_LANGUAGE=Spanish')); END; / Domingo , 07TH Agosto 2011

另外为了去除显示结果中多余的0和空格，我可以使用FM元素修饰符。

BEGIN DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, 'FMDay, DDth Month YYYY')); END; / Sunday, 7TH August 2011

You can also use the format mask to extract just a portion of, or information about, the date, as shown in the following examples:
你还可以使用格式参数仅提取日期的一部分内容：
What quarter is it? 当前时间的下一时刻？

TO_CHAR (SYSDATE, ‘Q’)

SCOTT@orcl> select sysdate from dual; SYSDATE ------------------- 2015-07-25 06:37:17 SCOTT@orcl> select to_char(sysdate, 'Q') from dual; T - 3

What is the day of the year (1-366) for today’s date? 当前日期处于所在年份第几天？

TO_CHAR (SYSDATE, ‘DDD’)

SCOTT@orcl> select TO_CHAR (SYSDATE, 'DDD') from dual; TO_ --- 206

What are the date and time of a DATE variable?

BEGIN DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS')); END; /

You can also use EXTRACT to extract and return the value of a specified element of a date. For example
还可以使用EXTRACT提取日期指定元素值：

What year is it? 当前年份？

EXTRACT (YEAR FROM SYSDATE)

SCOTT@orcl> select EXTRACT (YEAR FROM SYSDATE) from dual; EXTRACT(YEARFROMSYSDATE) ------------------------ 2015

What is the day for today’s date? 所在当前月份的天数？

EXTRACT (DAY FROM SYSDATE)

SCOTT@orcl> select EXTRACT (DAY FROM SYSDATE) from dual; EXTRACT(DAYFROMSYSDATE) ----------------------- 25

如何将字符串转换为日期？使用to_date或to_timestamp内建函数。

DECLARE l_date DATE; BEGIN l_date := TO_DATE ('12-JAN-2011'); END ;

If the string you provide does not match the default format, Oracle Database will raise an exception:
注意：如果你提供的字符串参数与数据库或会话设置的格式参数模型不一致，Oracle数据库将抛出异常：

DECLARE l_date DATE; BEGIN l_date := TO_DATE ('January 12 2011'); END; / ORA-01858: a non-numeric character was found where a numeric was expected

You should not assume that the literal value you provide in your call to TO_DATE matches the default format. What if the format changes over time? Instead, always provide a format mask when converting strings to dates, as in
我们应该始终指定格式，因为你无法确定格式参数何时会改变。

例如：
l_date := TO_DATE (‘January 12 2011’, ‘Month DD YYYY’);

5、日期截取Date truncation

使用TRUNC内建函数来截取一个日期的指定单元。最常见的用法是TRUNC(DATE)-不指定任何参数。这时，TRUNC仅将time部分设置为00:00:00。
例如：

Set l_date to today’s date, but with the time set to 00:00:00:

l_date := TRUNC (SYSDATE);

获取当前日期所在月份第一天 Get the first day of the month for the specified date:

l_date := TRUNC (SYSDATE, ‘MM’);

Get the first day of the quarter for the specified date:

l_date := TRUNC (SYSDATE, ‘Q’);

获取当前日期所在年份的第一天 Get the first day of the year for the specified date:

l_date := TRUNC (SYSDATE, ‘Y’);

SCOTT@orcl> select TRUNC (SYSDATE, 'Y') from dual; TRUNC(SYSDATE,'Y') ------------------- 2015-01-01 12:00:00

6、日期运算 Date arithmetic

针对日期和时间戳的运算，Oracle数据库提供了以下方式：