[20111229]date time timestamp? ansi liternals.txt

今天看一篇文章，发现日期可以写成这样
date '2011-1-1'

google发现如下链接：
http://laurentschneider.com/wordpress/2005/11/ansi-literals.html

自己测试一下，一般我个人喜欢定义环境变量如下：

$ 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