Solve SQL date conversion problem

Working at a client on a SQL Server 2000 we imported data from a Progress database, among which are date values. Since Progress allows date formatted fields to have non-date values, during an import, SQL looks at the field format, expects dates and will report an error like "Error Description:Insert error, column" [your fieldname] "DBTYPE_DBTIMESTAMP), status 6: Data overflow".

A solution is to have an ActiveX script convert all values to valid dates when importing (and in this case we set all non date values to the maximum allowed date). In your DTS data transform task at the transformation tab, you enter code like (replace all [field name] with the field name of the date field you get errors on when importing, so only the field name without []):

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

' Copy each source column to the destination column
Function Main()
dim mydate
if isdate (DTSSource("[field name]")) = -1 then

mydate = cdate(DTSSource("[field name]"))
if ((mydate >= cdate("01/01/1753")) and (mydate <= cdate("31/12/9999"))) then
DTSDestination("[field name]") = DTSSource("[field name]")
else
DTSDestination("[field name]") = "12/31/9999"
end if
else
if isnull(DTSSource("[field name]")) then
DTSDestination("[field name]") = DTSSource("[field name]")
else DTSDestination("[field name]") = "12/31/9999"
end if
end if

Main = DTSTransformStat_OK
End Function

The values of 1753 and 9999 are the range of values SQL can handle. I ran into a small issue nevertheless, and found out that if your destination field is defined as smalldatetime, SQL has another range. I had to replace '01/01/1753' by '01/01/1900', and '31/12/9999' by '06/06/2079'.

(source of image: ebookee.com / pixhost.eu)