DATE type?

Printable View

05-03-2002, 06:50 AM
kgb

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]
05-03-2002, 08:53 AM
nandu

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
05-03-2002, 09:03 AM
kgb

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]
05-03-2002, 09:22 AM
kgb

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]
05-03-2002, 09:41 AM
nandu

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
05-03-2002, 10:06 AM
kgb

Thanks a lot !
I'll make a dump of ascii file now :-)