Dataype question

Question

Post reply

Dataype question

Jpotucek

SSCoach

Points: 18583
More actions
January 9, 2014 at 8:51 am

#302423

We are running SQL Server 2008 R2 and trying to load a Database with Data from another DBMS (DB2 LUW)
We are not running SSIS but the query tool that we are using wants to convert all of the TIMESTAMP datatypes in DB2 (multiple TIMESTAMP columns are allowed in the same table DB2) to a DATE datatype in SQL Server.
I created the table in SQL Server making all of the TIMESTAMP columns DATETIME and tried to load the table but I am getting this error:
22001: String data, right truncation
When I exclude loading the DATETIME columns, the load works fine. I tired changing DATETIME to VARCHAR for these columns and I get the same error
22001: String data, right truncation
Here is a sample of the data that I am trying to load into the DATETIME columns: 2011-04-26-08.02.51.036000
Any ideas on what datatype i would need to use to load this data?

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

You would need to use datetime2 for that much precision in the time.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

For SSIS, the data type should be DT_DATE or DT_DBTIMESTAMP2.

If you try to convert '2011-04-26-08.02.51.036000' to a datetime2 in SQL Server, you'll get an error. A different format is needed so SQL Server will be able to convert it.

I'm sure that SSIS can automatically convert DB2 TIMESTAMP to SQL Server datetime2, but if it doesn't, you'll have some work to do.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 3

Hi... You might find some help in this article:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/819a5a05-2aed-4c88-aa26-9a07cebcfb52/convert-a-db2-timestamp-to-a-sql-server-2000-date-format-?forum=transactsql

It seems that you may be better recasting the timestamp in the select from the DB2 server, so that SSIS has an easier time converting the value.

Thanks

John.

Jpotucek SSCoach Points: 18583 More actions · Answer 4

Thanks for all the help.

We do not have SSIS installed so that is not an option at this point