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 |
|
cronid
Starting Member
26 Posts |
 Posted - 2007-02-26 : 08:37:21
|
| I am loading a table from a pipe delimited text file and getting a datetime conversion error. The text field value string is 31 characters as follows: 20070205 07:45:15.136141 -0500 and the receiving column is defined as datetime. The failing text rows all contain valid 31 character dates. I know I can define the receiving table column as varchar and convert it after the fact, but shouldn't a straight DTS copy work? Obviously, it doesn't but why?Thanks for any help. |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-02-26 : 08:44:19
|
| The date string will have to be converted implicitly to a DATETIME value. SQL Server won't understand those strings without some transformation. You could do an ActiveX on that column in your datapump task to take the first 21 characters. However, if you have any significant volumes, it's likely to be quicker to do as you suggest, and put it into a staging table as a VARCHAR, and handle the conversion in T-SQL.Mark |
 |
|
|
cronid
Starting Member
26 Posts |
Posted - 2007-02-26 : 09:26:20
|
| Thanks - that works. |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-02-26 : 09:59:56
|
| Cool, but which approach? Did you do the ActiveX transform on the column? Out of interest, how many rows are you processing, since this will execute row-by-row?Mark |
|
|
|
|
|
|
DTS datetime conversion error