Results 1 to 5 of 5

Thread: Date Formatting

  1. #1
    Join Date
    Oct 2003
    Posts
    14

    Unanswered: Date Formatting

    Many thanks to everyone for their help with the last post.

    I have a further question. I have the following SELECT statement which functions fine:

    select
    LAST_UPDATE_DATE
    ,CREATION_DATE
    ,ENABLED_FLAG
    ,TO_DATE(START_DATE_ACTIVE,'YYYY-MM-DD')
    ,TO_DATE(END_DATE_ACTIVE,'YYYY-MM-DD')

    However, I would like the Date fields to be in the format DD-MM-YYYY but when I try to do this I get an error:

    ORA-01858: a non-numeric character was found where a numeric was expected

    The orginal data is stored in a VARCHAR2(10) field in the format '2003-10-09'.

    Thanks for your assistance
    Mark

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    I'm afraid you are mixing up the format of your output (what you would like to have on screen), and the format used in the to_date function to indicate how the character value is stored.

    You have two solutions :

    1. TO_CHAR(TO_DATE(START_DATE_ACTIVE,'YYYY-MM-DD'),'DD-MM-YYYY')

    2. Change the NLS_DATE_FORMAT of your session. If you are using windows, and if I'm not mistaking, you will have to operate this change in the registry. I don't think the following will work on Windows (ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY'), but I need to check this out.

  3. #3
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Whoops, I'm back.

    I did the following test on my Windows98 laptop

    SQL> select sysdate from dual;

    SYSDATE
    ---------
    23-OCT-03

    SQL> alter session set nls_date_format = 'YYYY/MM/DD';

    Session altered.

    SQL> SELECT SYSDATE FROM DUAL;

    SYSDATE
    ----------
    2003/10/23

    So, my guess is you should be able to change the NLS_DATE_FORMAT in your session without any problems. If you see that your ALTER SESSION... has no impact, than check out if there is any value set in the registry (LOCAL_MACHINE/SOFTWARE/ORACLE...)

  4. #4
    Join Date
    Oct 2003
    Posts
    14
    Thank you for that. Will give that a try. Curious to know how you are running Oracle on a Windows 98 Laptop

    Just intrigued really....

  5. #5
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Originally posted by solent
    Thank you for that. Will give that a try. Curious to know how you are running Oracle on a Windows 98 Laptop

    Just intrigued really....
    Personal Oracle 8i, that's all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •