Oracle date time literals

Every RDBMS has it's own format for specifying dates, times, and time stamp literals. When writing adhoc queries it's easiest if you can directly type in a date in the format that the database expects, without having to resort to a cast. When writing code, one should attempt to avoid converting times to and from string representation.

The following all are valid for comparisons against a date field.

<geshi lang="sql"> select * from WORK_LOG where start_time > TIMESTAMP'2009-07-01 00:00:00.000' order by start_time; select * from WORK_LOG where start_time > TIMESTAMP'2009-07-01 00:00:00' order by start_time; select * from WORK_LOG where start_time > TIMESTAMP'2009-07-01 0:0:0' order by start_time; select * from WORK_LOG where start_time > to_date('01/07/2009','DD/MM/YYYY') order by start_time; </geshi>