Does Oracle's “date'[yyyy-mm-dd]'” lit

Question

Rephrased, given I use date like so:

date'2010-04-10'

, could the outcome be anything but April 10th 2010 (e.g. October 4th 2010)?

UPDATE I hear what you, and the docs, say. But...

When a batch job runs, with hard-coded date'yyyy-mm-dd':s, it fails for some (non-deterministic) calculations. Running the failing SQL statements in PL/SQL Developer never yields the same, incorrect, value.

First, I ran the same calculations (hundreds of thousands) with the date'' date literals replaced with the to_date('', '') function, and everything worked just fine.

Then, I used NHibernate and its LINQ provider, and replaced the hand-crafted SQL altogether. ... and everything worked just fine... NHibernate produces a '' date literal, with the time part included, BTW.

UPDATE A colleague of mine wrote some code, that can reproduce the error in our environment. He posted his findings (including the code) at Oracle Forums: https://forums.oracle.com/forums/thread.jspa?threadID=2304569&tstart=0

UPDATE Changed the title according to zerkms' answer.

Answer 1

Here is a link to Oracle 10g SQL reference, on datetime literals. Excerpt:

The ANSI date literal contains no time portion, and must be specified in exactly this format ('YYYY-MM-DD'). Alternatively you can specify an Oracle date value, as in the following example:

TO_DATE('98-DEC-25 17:30','YY-MON-DD HH24:MI')

Answer 2

It is not function, but date literal. And, Yes, it always matches YYYY-MM-DD regardless any oracle settings.