How to change format date
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to change format date

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    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
    Jan 2004
    Posts
    162
    NEVER use TO_DATE on a DATE (it is for converting strings to DATEs). To display a DATE in a required format use TO_CHAR with appropriate date mask, e.g.
    Code:
    TO_CHAR (date_column, 'DD/MM/YYYY')

  3. #3
    Join Date
    Jul 2002
    Posts
    228
    Originally posted by padders
    NEVER use TO_DATE on a DATE (it is for converting strings to DATEs). To display a DATE in a required format use TO_CHAR with appropriate date mask, e.g.
    Code:
    TO_CHAR (date_column, 'DD/MM/YYYY')
    ok, but if I've
    to_date(sting_column,'DD/MM/YYYY') DATE_TODAY


    with this output :

    DATE_TODAY
    2/22/0005

    How Can I change it?

  4. #4
    Join Date
    Jan 2004
    Posts
    162
    If date was held in a VARCHAR2 / CHAR column in format DD/MM/YYYY (which incidentally is a VERY BAD IDEA) then you could convert it (on the fly) to a DATE using TO_DATE and the stored format and then TO_CHAR the result using the desired display format, e.g.
    Code:
    TO_CHAR (TO_DATE (string_column, 'DD/MM/YYYY'), 'MM/DD/YYYY')

  5. #5
    Join Date
    Sep 2003
    Location
    Detroit, MI
    Posts
    41
    You can take an idea about your NLS_DATE_FORMAT by this statement:

    *******************************************************************
    select value
    from nls_session_parameters
    where parameter = 'NLS_DATE_FORMAT';
    *******************************************************************

    And if you want to change it you can use this statement:

    *****************************************************************
    ALTER SESSION SET NLS_DATE_FORMAT = [THE NEW NLS_DATE_FORMAT]

    For Example:
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY'
    *****************************************************************

    Anyhow, I don't think that you're sure when execute TO_CHAR('DD-MM-YYYY', sysdate) you get the result in wrong way...

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