Originally posted by PSoni PLEASE READ IT AS
select max(to_date(datecolumn,'dd-mm-rrrr)) from atable ;

Still missing a quote here ;)

I believe datecolumn is a character column.

07-03-2003, 09:27 AM

jmodic

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;" ....

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):
03-JUL-03
This then converted back to date with -RRRR becomes:
03-JUL-2003
which is bigger than 31-MAY-2003

(All this can be avoided if you make sure your default date format includes YYYY).

. . on the other hand I could be wrong.

07-04-2003, 05:06 AM

TomazZ

Good one, I would never thought of this!

Quote:

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.