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

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

  1. #11
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by PSoni
    02-07-0003
    Even I wasn't born then!

    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

  2. #12
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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?

  3. #13
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    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?
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  4. #14
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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".)

  5. #15
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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
    Last edited by abhaysk; 07-06-2003 at 09:50 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #16
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    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
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

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