[20230207]ANSI DATE and TIMESTAMP SQL syntax.txt

[20230207]ANSI DATE and TIMESTAMP SQL syntax.txt --//以前我一直以为oracle仅仅支持ansi date语法,当然我很少使用.我个人比较喜欢直接使用字符串. --//只要NLS*环境变量与字符串日期顺序一致,一般不会存在任何问题,而且也不存在隐式转换. --//看了http://blog.tanelpoder.com/2012/12/29/a-tip-for-lazy-oracle-users-type-less-with-ansi-date-and-timestamp-sql-syntax --//才发现也支持timestamp的语法. --//简单测试看看. 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.测试例子: SCOTT@test01p> SELECT COUNT(*) FROM dba_objects WHERE created > DATE'2012-12-01';   COUNT(*) ----------      22529 SCOTT@test01p> select count(*) from dba_objects where created > date'2012-12-01';   COUNT(*) ----------      22529 SCOTT@test01p> select count(*) from dba_objects where created > date   '2012-12-01';   COUNT(*) ----------      22529     SCOTT@test01p> SELECT COUNT(*) FROM dba_objects WHERE created > DATE'2012/12/01'; SELECT COUNT(*) FROM dba_objects WHERE created > DATE'2012/12/01'                                                      * ERROR at line 1: ORA-01861: literal does not match format string SCOTT@test01p> SELECT COUNT(*) FROM dba_objects WHERE created > date'2012-01-01 08:12:34'; SELECT COUNT(*) FROM dba_objects WHERE created > date'2012-01-01 08:12:34'                                                      * ERROR at line 1: ORA-01861: literal does not match format string --//DATE'2012-12-01' 表示2012-01-01 00:00:00,注意并不区分大小写.另外注意分隔符号不能使用/等其它分割符号. --//date与日期之间可以存在空格. --//如果有时分秒我以前一直认为不行,实际上前面使用TIMESTAMP代替date. SCOTT@test01p> select count(*) from dba_objects where created > timestamp'2012-12-01'; select count(*) from dba_objects where created > timestamp'2012-12-01'                                                           * ERROR at line 1: ORA-01861: literal does not match format string SCOTT@test01p> SELECT COUNT(*) FROM dba_objects WHERE created > TIMESTAMP'2012-01-01 08:12:34';   COUNT(*) ----------      22529 SCOTT@test01p> select count(*) from dba_objects where created > timestamp'2012-01-01 08:12:34';   COUNT(*) ----------      22529 SCOTT@test01p> select count(*) from dba_objects where created > timestamp'2012-01-01 08:12:34.00001';   COUNT(*) ----------      22529 --//timestamp与date类似,不同在于timestamp的日期格式要输入时分秒.date不能输入时分秒. --//注意这里的执行计划可能存在隐式转换问题.因为timestamp类型比date类型高. --//我个人以前喜欢直接使用字符串. d:\tmp> set | grep -i nls NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS NLS_LANG=AMERICAN_AMERICA.ZHS16GBK NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF SCOTT@test01p> SELECT COUNT(*) FROM dba_objects WHERE created >  '2012-12-01';   COUNT(*) ----------      22529 SCOTT@test01p> SELECT COUNT(*) FROM dba_objects WHERE created >  '2012/12/01';   COUNT(*) ----------      22529 SCOTT@test01p> SELECT COUNT(*) FROM emp WHERE hiredate>  '1981:12:01 00/00/01';   COUNT(*) ----------          5 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  029m424dy0j35, child number 0 ------------------------------------- SELECT COUNT(*) FROM emp WHERE hiredate>  '1981:12:01 00/00/01' Plan hash value: 580533841 ----------------------------------------------------------------------------- | Id  | Operation        | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ----------------------------------------------------------------------------- |   0 | SELECT STATEMENT |         |        |       |     1 (100)|          | |   1 |  SORT AGGREGATE  |         |      1 |     8 |            |          | |*  2 |   INDEX SKIP SCAN| E_SORT1 |      5 |    40 |     1   (0)| 00:00:01 | ----------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / EMP@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("HIREDATE">TO_DATE(' 1981-12-01 00:00:01', 'syyyy-mm-dd               hh24:mi:ss'))        filter("HIREDATE">TO_DATE(' 1981-12-01 00:00:01', 'syyyy-mm-dd               hh24:mi:ss')) --//直接使用字符串不存在字符串转换问题,分割符号不一定是-,其它也可以.但是不支持timestamp的类型. SCOTT@test01p> SELECT COUNT(*) FROM emp WHERE hiredate>  '1981:12:01 00/00/01.000001'; SELECT COUNT(*) FROM emp WHERE hiredate>  '1981:12:01 00/00/01.000001'                                           * ERROR at line 1: ORA-01830: date format picture ends before converting entire input string