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
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?
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
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"
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
Tomaž "A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
Reply With Quote
Bookmarks