Moving data from DB2 to SQL Server has been an experience and a half, fraught with lessons throughout. My latest lesson is on dates. While running a query to import data, I kept running into this particular exception: <\/p>\n
Error converting data type DBTYPE_DBTIMESTAMP to datetime<\/strong><\/p>\n I figured there was some issue with one of the TIMESTAMP fields in the table. I quickly learned that DB2 called the date fields TIMESTAMPS, which I find bizzare since it means something different in SQL Server, but that’s another story. To temporarily get around the issue, I decided to exlude all of the date fields from the query so SQL Server wouldn’t choke. I ran it again. <\/p>\n Error converting data type DBTYPE_DBTIMESTAMP to datetime<\/strong><\/p>\n What the heck? How can SQL Server be having an issue with a field that it’s not getting? Without going into all the detail, here’s what I found out. When you run a query against a linked server, SQL apparently goes out to the linked server and gets all of the data. It then brings it back to SQL Server for processing. If you exlude a particular column in your SQL Query, SQL Server STILL has to deal with the column because it’s pulling it back from the linked server.<\/p>\n What had happened was someone botched a date (actually a few dates), accidentally entering 1\/1\/0200 instead of 1\/1\/2006. I don’t know what happened on 1\/1\/0200, but I know it was nothing relevant to what was in the database. Anyway, 1\/1\/0200 is a perfectly valid date for DB2 because it uses a 10 byte date value. SQL Server uses an 8 byte date value (don’t quote me on that), so the furthest back it goes is 1\/1\/1753, about 1500 years short of 1\/1\/0200. When DB2 passes 1\/1\/0200 to SQL Server, guess what you end up with? That’s right, Error converting data type DBTYPE_DBTIMESTAMP to datetime. <\/p>\n So, how the heck do you get around it? Fortunately, you have an option in the OPENQUERY method. The OPENQUERY method accepts two parameters, the name of a linked server, and a string containing the query you want to run. When it runs, it passes the text of the query to the linked server and the linked server processes it. This allows you to create a WHERE clause that excludes (or transforms if you want) dates that occur before 1\/1\/1753. Since the query runs on the DB2 server, it doesn’t explode. And since it only returns valid SQL dates, SQL Server doesn’t blow up when the results come back. Here’s the general idea:<\/p>\n SELECT * FROM OPENQUERY(DB2LinkedServerName, ‘SELECT * FROM [SomeTable] WHERE [SomeDateField] is NULL OR [SomeDateField] > ”1\/1\/1753”’);<\/p>\n Who knew. Hope this keeps someone from breaking their monitor in frustration. I came close.<\/p>\n","protected":false},"excerpt":{"rendered":" Moving data from DB2 to SQL Server has been an experience and a half, fraught with lessons throughout. My latest lesson is on dates. While running a query to import data, I kept running into this particular exception: Error converting data type DBTYPE_DBTIMESTAMP to datetime I figured there was some issue with one of the……<\/p>\n","protected":false},"author":46738,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-2354","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2354","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/46738"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2354"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2354\/revisions"}],"predecessor-version":[{"id":41421,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2354\/revisions\/41421"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2354"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2354"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2354"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2354"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}