DATE type?

[kgb]

Hi, guys

Can anybody explain me that result:

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]

[nandu]

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

[kgb]

Hi, the table structure is

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]

[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]

[nandu]

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

[kgb]

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