MS SQL Server - Date conversion on DTS

[samanw]

Hi All,
I need to transfer data from another database (IBM AS/400) into SQL Server, but I have a problem converting Date column from Numeric Data type into Date Data type,

Here is an activex I was trying to perform this task. But it gives me an error
(All dates are in MMDDYY format eventough the source column is Numeric data type)

Please help me.....

Sam
-----------------------------------------------------------------------------------------------

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

' Copy each source column to the destination column
Function Main()
strDate = DTSSource("REMDAT")
DTSDestination("REMDAT") = CDate(FormatDateTime(strDate, "MMDDYY"))
Main = DTSTransformStat_OK
End Function

[Klymer]

I am not the Master of VB Script, but the FormatDateTime Function doesn't allow your Arguments, but expects a number between 0 and 4 - take a look at msdn.
If you do not find a fitting format, then you have to build your String by yourself and pass that to CDate

[samanw]

Thanks but I inserted the right format like vbShortDate but still I get wrong values in the date field after the conversion. Thank you any way.