TO_DATE, TO_CHAR
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: TO_DATE, TO_CHAR

  1. #1
    Join Date
    Mar 2001
    Posts
    1
    Hi,

    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
    Posts
    462
    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
    Posts
    180

    Exclamation

    Hi,
    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.

    Regards
    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