Date Conversion

  • 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.




  • 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.


  • I run the sql statement through the DTS package--It is the "Execute SQL Task" icon within DTS.


  • 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.

  • 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...

  • Try importing the data into a varchar column and use isdate and where is not a valid date - do what you need to do.



    select * from table where isdate(date_column)=0

    Hopefully I've understood the problem and you can understand the answer.




    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