Re: Re: difference between max(datecolumn) and max(to_date(datecolumn),'dd-mm-rrrr')
This has two syntax errors - so I don't think this is what gave the output posted.
PLEASE READ IT AS
select max(to_date(datecolumn,'dd-mm-rrrr)) from atable ;
Are you 100% sure? The results suggest that it is a character column - 31 is the highest value for the first two characters.
Re: Re: Re: difference between max(datecolumn) and max(to_date(datecolumn),'dd-mm-rrrr')
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.
Tomaž "A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
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):
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).
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