April 19, 2005 at 1:05 pm
I have an issue with a date conversion. I am pulling in raw data from a .dbf file. There is a date of birth column, some of which is populated with actual Date of Births in the format 01/20/56, some columns are null and some are populated with 00/00/0000 (these denote companies rather than people). I am attempting to bring this in to a column entitled DOB_Raw. I then run a transact sql statement in the DTS package to change all of the nulls and 00/00/0000 to getdate(). I then want to move this data into a DOB field in another table that has the data type smalldatetime or datetime (either one). I have tried this changing the nulls and the 00/00/0000 and leaving them as is. Nothing works. I get this error: TransformCopy 'DTSTransformation_1' conversion error: Conversion invalid for datatypes on column pair 1 (source column 'DOB_Raw'(DBType_STR), destination column 'DOB'(DBType_DBTimestamp)). I have also tried using the convert date in the DTS package--also get the same error. I am unable to find the source date format in this instance.
Any help would be appreciated.
Thanks,
Paula
Paula
April 19, 2005 at 1:30 pm
DBType_DBTimestamp doesn't sound right for a datetime field (but I'm not that familiar with DTS).
Once you import it into a SQL extract table I'd run a sql statement to insert into your destination table rather than go out to DTS and back in to SQL.
April 19, 2005 at 1:32 pm
I run the sql statement through the DTS package--It is the "Execute SQL Task" icon within DTS.
Paula
April 19, 2005 at 1:40 pm
the message you indicate seems to be one from a transform data task. A sql error on an "insert into xxx select from..." statement would be different.
April 19, 2005 at 3:38 pm
Import the field into a char datatype field, then use the convert function to convert it. Also, you shouldn't have a table with company data and individual data, this is very non-normalized...
April 20, 2005 at 4:26 am
Try importing the data into a varchar column and use isdate and where is not a valid date - do what you need to do.
IE:
select * from table where isdate(date_column)=0
Hopefully I've understood the problem and you can understand the answer.
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply