Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2008
    Posts
    9

    Alter column data type from Date to Timestamp

    Hi,
    iam trying to alter table to change the data type of the column from Date to Timestamp, iam getting the following error

    16:15:04 [ALTER - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: TIMESTAMP;D_TIME SET DATA TYPE;<graphic_string>

    thanks in advance

  2. #2
    Join Date
    Jan 2003
    Posts
    4,126
    That is because it is not allowed. You will have to export the data out of the table, drop the table, recreate it with the new definition, and import the data.

    Note on the export, convert the date column to a timestamp in the select statement.

    Andy

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Alternatively, you could
    (1) rename the column
    (2) add a new column of type TIMESTAMP, with the old name
    (3) populate the new column with a casted copy of the old column
    (4) make the old column "hidden".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Change step (4) to "drop the old column" (assuming that the OP has DB2 V9.5, which supports that feature).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stolze
    Change step (4) to "drop the old column" (assuming that the OP has DB2 V9.5, which supports that feature).
    Not on DB2 9 for z/OS, unfortunately...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Oct 2008
    Posts
    77

    Cool Changing datatype

    Hello
    alter table tablename alter column columnname set datatype varchar(10)


    but it will not work in 8.1....

    this will work in 9.5

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yeah, since we don't know which version of DB2 on which platform the OP is using, we can only throw ideas around.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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