[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