Post reply

how?: date mapping problem from db2 to datetime

  • March 4, 2008 at 7:58 am

    #121044

    I need to automate a process such that my client (the data miner) can import db2 tables at her whim and create a corresponding sql table.

    I tried, using the import/export wizard from the 'IBM OLE DB Provider for DB2' to SQL with no success. It has problems converting DATE to datetime. It says the "The data type could not be assigned to the column "xxxxxx" in "Microsoft OLE DB Provider for SQL Server" The xml mapping in the sql mapping directory says the 'DATE' should be a simple convert to 'datetime'.

    If I can get the import/export working that would be great. Short of that, I need to be able to create the target table in SQL based on the DB2 table that is my source.

    Any suggestions?

  • April 21, 2008 at 2:47 pm

    #805208

    I am having the same issue. Any responce yet?

  • April 21, 2008 at 6:55 pm

    #805255

    DB2 date/times are much different than SQL Server (if memory serves me)... I believe they're based on something like the number of seconds since midnight 1970-01-01. You will need to make a conversion using something like...

    DATEADD(ss,db2column,'1970-01-01')

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 22, 2008 at 7:26 am

    #805510

    I was unable to get the mapping for the tables to automatically 'map'. I have to go into each table that I'm converting and tell each db2 date field to please be a sql datetime field.

    I did notice that Microsoft has an oledb2 provider in the 'Feature Pack for Microsoft SQL Server 2005 - February 2007'. I tried installing it - but it is not good for SQL 2005 standard edition so I was unable to see if it would map automatically.

    I would be interested in knowing if the 'Feature Pack' oledb2 provider worked for you.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply