Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    130

    Unanswered: TO_DATE('2007-04-03 23:59:59 ','YYYY-MM-DD hh24:mi:ss')

    When using TO_DATE('2007-04-03 23:59:59 ','YYYY-MM-DD hh24:mi:ss') in a INSERT command, corresponding field value is 2007-04-03 23:59:59.000000,

    a suffix string ".000000" is appended. How to avoid this.


    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    TO_DATE returns timestamp data type.
    The value of format-string(second parameter) is a template for how string-expression(first parameter) is interpreted and then converted to a timestamp value.

    If you want to remove a suffix string ".000000", you should do it by yourself, like...
    SUBSTR( CHAR( TO_DATE(.....) ) , 1 , 19 )

    But,
    what is the data type of the column in which you want to insert the data?

    If you want to insert the data into a column with timestamp data type,
    DB2 would automatically append the suffix, if you removed it.

    If you want to insert the data into a column with char(or varchar) data type,
    it is not necessary to convert to timestamp data type.
    You can insert the string '2007-04-03 23:59:59' directly into the column.

    If you want to insert the data into a column with date data type,
    it is not necessary to remove a suffix.
    (DB2 would remove it at the time to convert timestamp to date.)

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Post but

    "The TO_DATE function returns a timestamp from a character string that has been interpreted using a character template. TO_DATE is a synonym for TIMESTAMP_FORMAT"....

Posting Permissions

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