Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69

    Unanswered: Converting Oracle datetime to DB2 timestamp

    AIX 5
    DB2 8.2

    Spent half the day looking through different conversion articles and have not made any progress.


    I have a CSV from an oracle export that looks like this
    1204141165Ç1Ç3Ç0Ç50ÇIBMPS G_StorageEventÇ27-FEB-08 00:00:00ÇUM_SERVICESÇ169.30.6.45Çsl dn60vegrtp02ÇÇ

    I need to import into DB2 where the last_modified_time column is in DB2 timestamp format.

    So '27-FEB-08' needs to be converted into

    '2008-02-27-00.00.00.000000'


    I have tried using the IMPORT modified by parms

    db2 "IMPORT FROM /home/test.txt OF DEL MODIFIED BY timestampformat=\"DD-MM-YY\" COLDELÇ DELPRIORITYCHAR /home/test.msg INSERT_UPDATE INTO db2inst1.table"



    No luck. Any help with this command or a better way would be appreciated.


    Charlie

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you will be better off specifying an ISO-compliant date format on export.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    PKPChuck,
    I don't know if there is any way of converting timestamp in Oracle into ISO compilant format. Try looking CHAR function or something like that.

    But if you can't get data into correct format you can do that with SQL inside DB2. So import data to DB2 table and then execute select statement to get correct data.

    Sample:
    create table admin.tab (col1 char(20))
    insert into admin.tab values ('27-FEB-08')

    select timestamp('20' concat substr(col1,8,2) concat '-' concat case substr(col1,4,3) when 'JAN' THEN '01' WHEN 'FEB' THEN '02' END concat '-' concat substr(col1,1,2) concat '-00.00.00.000000') from admin.tab

    The output is: 2008-02-27-00.00.00.000000
    Note: continue case syntax for all months.

    Hope this helps,
    Grofaty

Posting Permissions

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