Problem solve Get help with specific problems with your technologies, process and projects.

Getting type mismatch error converting string to date

I am using a DTS package to load a table from a text file. One of the transformations involves transforming a string (in the format of mm/dd/yyyy) into a datetime field in the same format (mm/dd/yyyy). Since the source field field is character and the destination field is datetime, I could not of course simply do a copy transformation. Neither would SQL Server allow me to do a simple datetime transformation. As a result, I have been experimenting with trying an Active X transformation using the CDate() function in an effort to convert the string to a date. The only problem is that I keep getting a Cdate type mismatch error, and I don't know how to resolve it. Currently this is how the Active X transformation looks:
 Function Main() DTSDestination("Date")=CDate (DTSSource="Col011")) Main = DTSTransformStat_OK End Function
Could you please shed some light on why I keep getting the type mismatch error and what I might try to correct it?
You should be able to simply use a convert function to convert the data. If there is an error generated, it is because one or more of the rows contains an invalid date.

Dig Deeper on Microsoft SQL Server Installation

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.