Tired of all the typing required to specify a date in a query using TO_DATE?

SELECT SUM(sale_amt) AS december12_sales 
WHERE sale_date BETWEEN to_date('2012-12-01','YYYY-MM-DD') AND to_date('2012-01-01','YYYY-MM-DD');

Whew! Shake out your fingers, that was a pain. Fortunately, there’s an easier way.

Oracle supports the ANSI date datatype, and the syntax to specify an ANSI date literal is much easier on the fingers:

DATE 'YYYY-MM-DD'

reducing the previous query’s typing to

SELECT SUM(sale_amt) AS december12_sales 
WHERE sale_date BETWEEN DATE '2012-12-01' AND DATE '2012-01-01';

Much less typing required!

The ANSI date datatype doesn’t include hours, mins, seconds or fractional seconds; DATE ‘2012-01-01’ means ‘2012-01-01 00:00:00’ . These can be specified using a TIMESTAMP literal:

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'

Tanel Poder has a nice blog post on this which I found while googling for the doc links.