今天看一篇文章,发现日期可以写成这样
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
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-714159/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-714159/