difference between max(datecolumn) and max(to_date(datecolumn),'dd-mm-rrrr')
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    125

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

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

    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.

    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.

  3. #3
    Join Date
    Feb 2001
    Posts
    125

    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

  4. #4
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422

    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Feb 2001
    Posts
    125
    SQL> desc atable
    Name Null? Type
    ------------------------------- -------- ----
    VRNO VARCHAR2(11)
    DATECOLUMN DATE



    text file attached
    Attached Files Attached Files
    • File Type: txt t1.txt (977 Bytes, 347 views)

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.
    Last edited by DaPi; 07-04-2003 at 04:09 AM.

  8. #8
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Good one, I would never thought of this!

    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

  9. #9
    Join Date
    Feb 2001
    Posts
    125
    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>
    Attached Files Attached Files

  10. #10
    Join Date
    Feb 2001
    Posts
    125
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width