Oracle date time literals

From EggeWiki

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>

My fun can be had passing a date into Oracle through Informatica. In Informatica you can have a parameter type be a string or a date. If you choose string, you simply have to pass it in the above format. If you choose date, then you have to guess the Informatica date format. For dates, Informatica offers this advise:

Using Datetime Parameters and Variables When you use a datetime parameter or variable in the Source Qualifier transformation, you might need to change the date format to the format used in the source.

If you get the date wrong, you'll receive this error: <geshi> [VAR_27056 [Data conversion error in converting [2009-07-09].] </geshi>

I found that putting the date in American format worked. That is MM-DD-YYYY <HH:MM:SS>.