Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: How to change format date

    Hi,
    I've run this query:

    select to_date(sysdate,'DD/MM/YYYY') DATE_TODAY
    from dual

    my output is:

    DATE_TODAY
    2/22/0005

    but I'd like to get this output (with 'DD/MM/YYYY' format):

    DATE_TODAY
    22/2/2005

    How Can I change the date format?
    Must I change the format server-side?

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    You are confusing Oracle. The value of SYSDATE is already a date, so why are you using the TO_DATE function on it. Just replace the TO_DATE function with the TO_CHAR function and your query will work correctly.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    Quote Originally Posted by markrem
    You are confusing Oracle. The value of SYSDATE is already a date, so why are you using the TO_DATE function on it. Just replace the TO_DATE function with the TO_CHAR function and your query will work correctly.
    ok, but if I've
    to_date(string_column,'DD/MM/YYYY') DATE_FORMAT


    with this output :

    DATE_FORMAT
    2/22/0005

    How Can I change it?

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Post the results to these (from sqlplus)

    sql> sho parameter nls_date

    sql> select to_char(sysdate,'MM/DD/YYYY') FROM DUAL;

    sql> select to_char(sysdate,'DD-MON-YYYY') FROM DUAL;

    Gregg

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It is because your nsl date settings, try doing something like: alter session set nls_date_format = 'dd/mm/yyyy'

  6. #6
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    The original problem was caused by using SYSDATE as an argument in your TO_DATE function. Now that you have revised the question to using a string representation of a date, you can keep the TO_DATE function and use the format mask as described by the other posters.

    Because you are now using a string representation of a date, you must supply a format mask twice. The first time tells the TO_CHAR function how to read your string date, and the second mask is the one you originally wanted for displaying the date a certain way.

    For example,
    select TO_CHAR(TO_DATE('02-JAN-2005','dd-MON-yyyy'),'dd/mm/yyyy') from dual;
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

Posting Permissions

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