DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2001

    I've recently migrated into the Oracle RDBMS, I need your help in understanding the differences between to_date and to_char functions in Oracle.

    I always confuse myself as to what these functions actually do and when to use them especially when it relates to Date fields in the database.

    Greatly appreciate all feedback. Would help me a great deal if an example is provided.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2000
    TO_DATE() converts a character string into a date. The syntax is:

    TO_DATE(string_date, optional_format_string)

    An example:

    select TO_DATE('01/03/2001','DD/MM/YYYY') from dual;


    TO_CHAR() converts a date into a character string. The syntax is:

    TO_CHAR(date_value, optional_format_string);

    An example:

    select TO_CHAR(sysdate,'DD/MM/YYYY') from dual;

    NOTE: the optional_format_string is optional only if the string_date is in the NLS_DATE_FORMAT for the session.

  3. #3
    Join Date
    Feb 2001


    Be aware of this little trap :
    When using a decode for instance to eliminate default values,
    make sure the format is oke:

    SELECT decode( t.input_date
    , to_date('19000101','yyyymmdd')
    , to_date(Null)
    , t.input_date)
    FROM MyTable t;

    When not using a to_date(Null), it may be converted to char
    (when it is the first record) and thereby truncate the real dates. When standard representation = mm/dd/yy
    a date like 02/15/99 may become 02/15/2099 after this selection.

    Ben de Boer

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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.