Can't you please post the output of "DESCRIBE atable" from SQL*Plus session?
When you say: "YES ITS DATE COLUMN", do you think anyone will belive you? I mean, with so many mistakes you have posted here in a simple "select max(to_date(datecolumn,'dd-mm-rrrr')) from atable;" ....
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
My guess is that your data includes two dates:
31-MAY-2003 and 03-JUL-1903
MAX(datecolumn) gives the correct value: 31-MAY-2003.
What I think is happening in the second expression is:
For the TO_DATE to work, datecolumn is somehow first converted to characters (it expects character input NOT a date - I'm surprised it does not give an error):
This then converted back to date with -RRRR becomes:
which is bigger than 31-MAY-2003
(All this can be avoided if you make sure your default date format includes YYYY).
Originally posted by DaPi For the TO_DATE to work, datecolumn is somehow first converted to characters (it expects character input NOT a date - I'm surprised it does not give an error)
Somehow means that it's implicitly converted to characters.
If you input to_date function a date format that is compatible with default setting (used for implicit to_char conversion), it gives no error.
Tomaž "A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams