• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1117
  • Last Modified:

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?
2 Solutions
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.
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now