[20111229]date time timestamp? ansi liternals.txt

今天看一篇文章，發現日期可以寫成這樣
date '2011-1-1'

google發現如下連結：


自己測試一下，一般我個人喜歡定義環境變數如下：

$ export | grep NLS
declare -x NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
declare -x NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
declare -x NLS_TIMESTAMP_FORMAT="YYYY-MM-DD HH24:MI:SS.FF"
declare -x NLS_TIMESTAMP_TZ_FORMAT="YYYY-MM-DD HH24:MI:SS.FF"
declare -x NLS_TIME_FORMAT="HH24:MI:SS.FF"

select date '2012-1-2' from dual;
DATE'2012-1-2'
-------------------
2012-01-02 00:00:00

並且如果寫成

SQL> select date '2012/1/2' from dual;
            *
ERROR at line 1:
ORA-01861: literal does not match format string

感覺根本不使用/分割.並且date 不能使用時分秒。

SQL> select date '2012-1-2 15:12:39'  from dual;
select date '2012-1-2 15:12:39'  from dual
            *
ERROR at line 1:
ORA-01861: literal does not match format string

還有其他的如time,timestamp.

SQL> select time '12:23:21' ,timestamp '2012-1-1 12:23:34'from dual;

TIME'12:23:21'         TIMESTAMP'2012-1-112:23:34'   
---------------------- ------------------------------
12:23:21.000000000     2012-01-01 12:23:34.000000000

不允許兩者相加,例子:

SQL> select date '2012-1-1' + time '12:23:21' from dual;
select date '2012-1-1' + time '12:23:21' from dual
                       *
ERROR at line 1:
ORA-30087: Adding two datetime values is not allowed

可以這樣相加,例子:
SQL> select date '2012-1-2'+ interval '15' hour from dual ;
SQL> select date '2012-1-2'+ to_dsinterval('0 2:0:0') from dual;
SQL> select date '2012-1-2'+ 1/24 + 15/1440 + 40/86400 from dual;

DATE'2012-1-2'+1/24
-------------------
2012-01-02 01:15:40