Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: Alter Table GUI: Date to Timestamp conversion

    DB2 ESE v8.1.14.292 FP14, Win 2k3

    I've searched the forums and havent found a post which "directly" addresses my question, maybe I can get some help.

    I'm attempting to change a DATE column to a TIMESTAMP using the "alter table" gui in cc (which appears to use SYSPROC.ALTOBJ). I understand I can do this manually but I figured something so simple as changing a date to a timestamp is something this should handle, right?

    Created a simple table, 2 columns: varchar(10), date
    Added 1 row, values: 001, 2008-01-12

    In the "Conversion expression (date to timestamp)" box I've tried:
    TIMESTAMP(DATE_COLUMN,'00:00:00')
    TIMESTAMP_ISO(DATE_COLUMN)

    which both work successfully if I use them in a select, but on the LOAD portion of the alter I get status INVALID. When I check the temp table created by ALTOBJ the data is still stored as DATE, which is why I'm assuming it fails on the import. Is there some trick to getting the Conversion expression working for timestamps? Can anyone shed some light on this?

    Thanks in advance.

    -min

  2. #2
    Join Date
    Jul 2009
    Posts
    150
    Did you use same column in which you loaded for load ?

    This is a possible for update, not for insert.

    Also the best way to change a TYPE of the column is
    DROP + CREATE table instead of ALTER.

    Kara

  3. #3
    Join Date
    Dec 2008
    Posts
    76
    Personally, I don't like using black box procedures for DDL. I don't trust them.

    They are going through the same procedures you would, but if there is a problem you are left guessing at the cause. It's much more predictable to do it yourself.
    RD

  4. #4
    Join Date
    Sep 2009
    Posts
    4
    Thanks for the replies.

    Yeah, you're right about the black box method, I guess the last thing I want to do is send my data down a path of uncertainty. It’s unfortunate we can’t rely on these built-in features provided by IBM for a quick fix.

Posting Permissions

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