DATE type?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: DATE type?

  1. #1
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684

    Angry 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

  2. #2
    Join Date
    May 2002
    Posts
    108

    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

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684

    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

  4. #4
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    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

  5. #5
    Join Date
    May 2002
    Posts
    108

    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

  6. #6
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    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
  •  



Click Here to Expand Forum to Full Width