-
HOW to use TO_CHAR() function?
Hi, guys
Can anybody explain me that result:
Code:
select '>'||to_char(t$invd,'YYYY')||'<', to_char(t$invd,'DD.MM.YYYY'),t$invd,sysdate from baandb.TTDSLS045550 where rownum < 2 and t$orno=41;
'>'||T TO_CHAR(T$ T$INVD SYSDATE
------ ---------- --------- ---------
>0000< 00.00.0000 01-JAN-99 03-MAY-02
It seems to me function to_char does not return correct result or may be I do not understand somethink?
field t$invd has DATE format.
Oracle 8.1.6, SUN, Solaris.
The same query works fine on Oracle 7.3 (WinNt)
Thanks.
[Edited by kgb on 05-03-2002 at 07:22 AM]
Best wishes!
Dmitri
-
Check with SYSDATE
Hi
If you have not tried, can you please try some simple formatting for SYSDATE with the TO_CHAR function, selecting it from DUAL?
If it works fine, it should be something specifically
related to the Table's data/data type.
Please post your reply.
Cheers
Nandu
Never give up !
Nanda Kumar - Vellore
-
Re: Check with SYSDATE
Hi, the table structure is
Code:
SQL> desc TTDSLS045550;
Name Null? Type
----------------------------------------- -------- ----------
.........
T$DINO NOT NULL NUMBER
T$INVD NOT NULL DATE
T$INVN NOT NULL NUMBER
......
----------------------------------------------------------
select T$INVD, to_char(T$INVD,'dd.mm.yyyy'), to_char(sysdate,'dd.mm.yyyy') from TTDSLS045550 wh ....
SQL> /
T$INVD TO_CHAR(T$ TO_CHAR(SY
--------- ---------- ----------
01-JAN-99 00.00.0000 03.05.2002
------------------------------------------------
1* select T$INVD, to_date(to_char(T$INVD,'dd.mm.yyyy'),'dd.mm.yyyy') from TTDSLS045550 where rownu
SQL> /
select T$INVD, to_date(to_char(T$INVD,'dd.mm.yyyy'),'dd.mm.yyyy') from TTDSLS045550 where rownum < 2
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
-----------------------------------------------
May be the way I got that table can tell you something:
1) Export from Oracle 7.3 (WinNt),
2) Import to Oracle 8.1.5 (NT)
3) Export from 8.1.5 (NT)
4) Import to 8.1.6 (SUN)
During all steps I did not recieve any error messages.
Thank you very much.
[Edited by kgb on 05-03-2002 at 09:09 AM]
Best wishes!
Dmitri
-
I have found that during import from Oracle 7.3 to Oracle 8.1.5 fields which has DATE type got strange problem, but not all of them.
connect baandb/baan@ora815;
SQL> select T$INVD, to_char(T$INVD,'dd.mm.yyyy'), to_char(sysdate,'dd.mm.yyyy') from TTDSLS045550 wh
ere rownum < 2;
T$INVD TO_CHAR(T$ TO_CHAR(SY
--------- ---------- ----------
01-JAN-99 00.00.0000 03.05.1998
SQL> connect baandb/baan@ora73;
Connected.
1* select T$INVD, to_char(T$INVD,'dd.mm.yyyy'), to_char(sysdate,'dd.mm.yyyy') from TTDSLS045550 wh
SQL> /
T$INVD
---------
TO_CHAR(T$INVD,'DD.MM.YYYY')
---------------------------------------------------------------------------
TO_CHAR(SYSDATE,'DD.MM.YYYY')
---------------------------------------------------------------------------
01-JAN-99
01.01.9999
12.04.1998
[Edited by kgb on 05-03-2002 at 09:25 AM]
Best wishes!
Dmitri
-
Irrational date behaviour- Thread Open?
Hi
There have been problems with date behaviour
See thread 16743 to find more about date
behaviour.
Still it can't be explained why it returns 00.00.0000 even if the date be 01-JAN-9999.
Is this thread still open?
Cheers
Nandu
Never give up !
Nanda Kumar - Vellore
-
Thanks a lot !
I'll make a dump of ascii file now :-)
Best wishes!
Dmitri
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
|