difference between max(datecolumn) and max(to_date(datecolumn),'dd-mm-rrrr')

Printable View

07-03-2003, 01:22 AM
PSoni

difference between max(datecolumn) and max(to_date(datecolumn),'dd-mm-rrrr')

hi all

when i use
select max(datecolumn) from atable ;

it gives

31-may-03

but when

select max(to_date(datecolumn),'dd-mm-rrrr) from atable ;

it gives
03-jul-03

second query is giving correct result.

please note that type datecolum is date

what is the difference ???

we r using win2000 and oracle 8.1.7

P.soni
07-03-2003, 03:08 AM
DaPi

Re: difference between max(datecolumn) and max(to_date(datecolumn),'dd-mm-rrrr')

Quote:

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

This has two syntax errors - so I don't think this is what gave the output posted.

Quote:

Originally posted by PSoni
please note that type datecolum is date

Are you 100% sure? The results suggest that it is a character column - 31 is the highest value for the first two characters.
07-03-2003, 05:23 AM
PSoni

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.

YES ITS DATE COLUMN

P.SONI
07-03-2003, 08:38 AM
TomazZ

Re: Re: Re: difference between max(datecolumn) and max(to_date(datecolumn),'dd-mm-rrrr')

Quote:

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;" ....
07-04-2003, 01:12 AM
PSoni

1 Attachment(s)

SQL> desc atable
Name Null? Type
------------------------------- -------- ----
VRNO VARCHAR2(11)
DATECOLUMN DATE

text file attached
07-04-2003, 03:07 AM
DaPi

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.
07-04-2003, 07:13 AM
PSoni

1 Attachment(s)

i have run query using format 'YYYY' output is something like
below

perhapes copy-paste is not proper... so i am also attaching text file

SQL> select to_char(min(to_date(datecolumn,'dd-mm-yyyy')),'dd-mm-yyyy'),to_char(max(to_date(datecolumn,'dd-mm-yyyy')),'dd-mm-yyyy')
2 from atable
3
SQL> /

TO_CHAR(MI TO_CHAR(MA
---------- ----------
16-05-0003 02-07-0003

SQL> select to_char(min(datecolumn),'dd-mm-yyyy'),to_char(max(datecolumn),'dd-mm-yyyy')
2 from test.atable
3
SQL> /

TO_CHAR(MI TO_CHAR(MA
---------- ----------
16-05-2003 31-05-3003

1* select to_char(max(to_date(datecolumn,'dd-mm-yyyy')),'dd-mm-yyyy') from atable
SQL> /

TO_CHAR(MA
----------
02-07-0003

SQL>
07-04-2003, 07:29 AM
PSoni

we are noticing that by setting the session

nls_date_format

we are getting correct result

the old nls_date_format was 'DD-MON-YY'

new session is 'DD-MM-YYYY'

I want to know if procedure/fuction will be affected by the session's parameter .

soni
07-04-2003, 07:52 AM
DaPi

Quote:

Originally posted by PSoni
02-07-0003

Even I wasn't born then!

Quote:

Originally posted by PSoni
to_date(datecolumn,'dd-mm-yyyy')

This is TOTALLY SKREWY. Why are you doing this? If datecolumn is a DATE you don't need to convert it! If you want to trim off the time part, TRUNC(datecolumn) will do it.

If you want to do date manipulations in procs, then I would insist that any convertions were coded with an explicit format. If you don't agree then we will send round Mr.Hanky to sort you out ;)
07-04-2003, 05:05 PM
DaPi

Quote:

Originally posted by TomazZ
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.

Wasn't sure about that and didn't have time to experiment till I got home. Here it's under 7.3.4:

Code:

SQL> select sysdate from dual; SYSDATE --------- 04-JUL-03 SQL> select to_date(sysdate,'DD-MM-RRRR') from dual; ERROR: ORA-01858: a non-numeric character was found where a numeric was expected

This is the kind of error I expected - due to a mismatch between MM and MON ('DD-MON-RRRR' works OK). Do vsn's 8+ do better?
07-05-2003, 07:05 PM
TomazZ

Quote:

Originally posted by DaPi
Do vsn's 8+ do better?

8+ same. It seems logical this way. If format is same, it's ok, else ... user's fault.
But as you mentioned, what's the point of to_date-ing a date column?
:confused:
07-06-2003, 06:55 AM
DaPi

Quote:

Originally posted by TomazZ
8+ same. It seems logical this way. If format is same, it's ok, else ... user's fault.

So why didn't PSoni get this error? (Which is why I said "somehow".)
07-06-2003, 09:38 AM
abhaysk

Quote:

Originally posted by DaPi
So why didn't PSoni get this error? (Which is why I said "somehow".)

Code:

SCS_DBA@U144> select * from v$version; BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production PL/SQL Release 8.1.7.4.0 - Production CORE 8.1.7.2.1 Production TNS for 32-bit Windows: Version 8.1.7.4.0 - Production NLSRTL Version 3.4.1.0.0 - Production SCS_DBA@U144> select * from NLS_SESSION_PARAMETERS where PARAMETER='NLS_DATE_FORMAT'; PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_DATE_FORMAT DD-MON-YY SCS_DBA@U144> select to_date(sysdate,'DD-MM-RRRR') from dual; TO_DATE(S --------- 06-JUL-03
07-07-2003, 05:06 AM
TomazZ

Yep, my default nls_date_format is 'dd.mm.rr', so i tried other way around:

Code:

SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production PL/SQL Release 8.1.7.2.0 - Production CORE 8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production SQL> select * from NLS_SESSION_PARAMETERS where PARAMETER='NLS_DATE_FORMAT'; PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_DATE_FORMAT DD.MM.RR SQL> select to_Date(sysdate,'dd.mon.rr') from dual 2 ; select to_Date(sysdate,'dd.mon.rr') from dual * ERROR at line 1: ORA-01843: not a valid month

But when I do it this way, it works (looks like 'mm' can read 'mon' format also)

Code:

SQL> alter session set nls_date_format='DD-MON-YY'; Session altered. SQL> select to_date('07-JUL-03','dd.mm.yyyy') from dual; TO_DATE(' --------- 07-JUL-03

Then I did a little homework (RTFM) and found:
http://download-west.oracle.com/docs...elem.htm#35494