unable to convert MySQL date to SQL Server smalldatetime

[Stanislav Godin]

Hello,
I am using a DTS package to import tables from MySQL to SQL Server 2000. All works fine except tables that have date type columns (MySQL). The format is YYYY-MM-DD. The receiving fields in MS SQL are smalldatetime. I am getting the following error: ....general conversion failure on column pair ... (source column 'end_date' (DBTYPE_DBDATE)), (destination column 'end_date' (DBTYPE_DBTIMESTAMP).

I then used ActiveX script to convert the dates:

'****************************************************************
' 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("start_date") ,9 , 2 ))
i_Month = Cint(Mid( DTSSource("start_date") ,6 , 2 ))
i_Year = Cint(Left(DTSSource("start_date"),4))

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

I get an error: ......Error description: Type mismatch: 'DTSSource'.

Then I tried casting in Transform Data Task:

select `program_id`,`campaign_id`,`program_name`,`description`,cast(`start_date` as datetime),cast(`end_date` as datetime) from `factivaupd`.`programs`

I am getting a different error: ....insert error, column 6('end_date', DBTYPE_DBTIMESTAMP), status 6: data overflow. Invalid character value for cast specification.

I have not clue why convertion can not happen. Any help will be greatly appreciated.

Thanks,
Stan