date or timestamp literals

Yesterday I commented on a post on the French developer forums http://www.developpez.net about using DATE and TIMESTAMP literals.

A date literal is DATE '2000-01-01'. It is not possible to have a date literal at 6am, like DATE '2000-01-01 06:00:00', the syntax simply does not exist. You can either use TO_DATE function, or if you are really hooked on litterals, maybe DATE '2000-01-01'+INTERVAL '+0 06:00:00' DAY TO SECOND.

One suggestion was to use TIMESTAMP, because TIMESTAMP literals do have time in it.

However, comparing a date with a timestamp will result in an implicit conversion to timestamp, and if the date colum is indexed, the index will not be used.

SQL> create table lsc_t(x number, d date)
Table created.
SQL> create index lsc_i on lsc_t(d)
Index created.
SQL> insert into lsc_t(d) values (to_date('2000-01-01 06:00:00',
'YYYY-MM-DD HH24:MI:SS'))
1 row created.
SQL> select * from lsc_t where d>=timestamp '2000-01-01 06:00:00'
X D
---------- -------------------
2000-01-01 06:00:00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=CHOOSE
1 0 TABLE ACCESS FULL TGPOWNER.LSC_T
SQL> select * from lsc_t where d>=to_date('2000-01-01 06:00:00',
'YYYY-MM-DD HH24:MI:SS')
X D
---------- -------------------
2000-01-01 06:00:00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=CHOOSE
1 0 TABLE ACCESS BY INDEX ROWID TGPOWNER.LSC_T
2 1 INDEX RANGE SCAN TGPOWNER.LSC_I


As shown above, the index is not used when comparing to timestamp.

Why does Oracle convert the date to timestamp ? Because timestamp may contain nanoseconds.
SQL> select * from lsc_t where
d>=timestamp '2000-01-01 06:00:00.000000001';

X D
---------- -------------------
2000-01-01 06:00:00


Converting to date would deliver wrong result.