Change a Date Format Using ActiveX and DTS

Lets say you are importing data via DTS and the date is in this format:
YYYYMMDD
So, SQL server isn't going to know what to do with this data. So, you're going to have to use ActiveX copy instead of the plain old Copy Column Task. Here is the code you would use in your ActiveX task within the Data Pump:

'**************************************
' Visual Basic Transformation Script
'**************************************

' Copy each source column to the destination column
Function Main()
DTSDestination("myDate") = left(DTSSource("badDate"),4)+"-"&_
+mid(DTSSource("badDate"),5,2)+"-"+right(DTSSource("badDate"),2)
Main = DTSTransformStat_OK
End Function

Substitute myDate for your destination and badDate for the source. This will give you a nice result in the YYYY-MM-DD format.

You can also expand this technique to include times. You would have to use some two more Mid to pull the date and hour, and the right would pull the minutes.

Happy Coding!