DB2 Timestamp to SQL Server Datetime

I'm importing data from a DB2 database to SQL Server 2005.

Everything is working fine, with the exception of when I try to import a DB2 Timestamp field.

I've tried to go from....

DB2 Timestamp to SQL Server 2005 SMALLDATETIME
DB2 Timestamp to SQL Server 2005 DATETIME

In both cases, the SQL Server Import fails saying that the Timestamp fields would be truncated.

When I look at the values in the DB2 Timestamp field, they are formatted as YYYY-DD-MM HH24:MI:SS.SSSSSSSSSSSSSSSSSS  with a lot of decimal places on the seconds (appears to be a lot more than SQL Server captures on the DATETIME field)

Thinking this was the issue, I used the following function in my original SQL statement that is run against DB2 to cut down on the # of decimal places...

varchar_format(Creation_Date_Timestamp,'YYYY-DD-MM HH24:MI:SS') as Creation_Date_Timestamp.  

This works fine going into the DATETIME field in SQL Server 2005, but I'd rather not have to do this on every date field in the database.

Is there a different variable type I should be using on the SQL Server side to import these values into?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Unfortunately, I am quite sure that is the only way you can do it.  SQL Server 2008 has higher precision for dates but on 2005 it is only up to about 3 milliseconds.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
how do you perform the import?
maybe there is a way to tell it to ignore the truncation
which type of db2 database?

i don't recall encountering this as a problem,when i used to do this (used datetime as receiving datatype)

from mainframe db2 and aix db2
tfewster500Author Commented:
I'm not sure on all the details on the DB2 side.  This system is remote so all I know is the information required for me to create an ODBC connection.  I believe it is OS400.

I'm just using the SQL Server 2005 data import wizard.  My data source is the ODBC connection DSN into a SQL Server 2005 database.  I'm forced to write an SQL statement as the data source data (I'm not offered the listing of tables on the DB2 side)
you can find explanation here how to set the wizard to ignore the truncation
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.