[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