When building tables people quite often use character fields to hold dates. A style that is quite often used to format the date is this yyyymmdd. When we come to take this data into a field of say smalldatetime then we can encounter errors.

Active X Scripting Transform 'DTSTransformation__1' encountered an invalid data value for col001 destination column

Insert Error, column 1 ('col001','DBTYPE_DBTIMESTAMP) status 6: data overflow. Invalid character for cast specification

DateTime columns can be further complicated by our regional settings. If our regional settings are set to UK English and we pass in a date of 10/24/2001 then it will fail because this would be interpreted as the tenth day of the twenty fourth month. It would not fail if our regional settings were set to US English though.

Inside our Active X script though we have a function which can interpret our dates easily

Here is an example of how to use it on our yyyymmdd formatted string

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

'  Copy each source column to the destination column
Function Main()

	dim i_Day
	dim i_Month
	dim i_Year


	i_Day = Cint(Mid( DTSSource("col1") ,7 , 2 ))
	i_Month = Cint(Mid( DTSSource("col1") ,5 , 2 ))
	i_Year = Cint(Left(DTSSource("col1"),4))




	DTSDestination("Col001") = DateSerial( i_Year , i_Month ,i_Day )
	Main = DTSTransformStat_OK
End Function