-
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
-
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?
-
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
-
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".)
-
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|