Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-04-26 : 09:32:55
|
| ks writes "I am trying to import DB2 tables to SQL Server 2000 using DTS. DB2 tables contain date fields that contain the value "0001-01-01" DTS does not like this date and gives an invalid date error message. I do not want to use varchar as I will not be able to use the date functions in reports. I have no control over the DB2 tables and these tables are huge. Is there any way to import these tables using shortdatetime in DTS?Thanks" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-04-26 : 09:42:04
|
| There's no way to make this date value fit a SQL Server date, it's out of the accepted range. You can write a DB2 query with a CASE expression to convert out-of-range dates:SELECT CASE WHEN myDate<'17530101' THEN Null ELSE myDate END as myDate, col1, col2 FROM myDB2Table |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-26 : 10:30:38
|
| orSELECT CASE WHEN IsDate(myDate)=0 THEN Null ELSE myDate END as myDate, col1, col2 FROM myDB2TableMadhivananFailing to plan is Planning to fail |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-04-26 : 18:20:59
|
| IsDate() is not a valid DB2 function. He would have to import is as varchar before he could run that test, which pretty much defeats the purpose. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-27 : 01:31:40
|
Thanks. I didnt know that DB2 doesnt support IsDate()  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
DB2 low dates (0001-01-01) and DTS