Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
 Posted - 2006-09-20 : 04:39:31
|
| Hi i was give this code to work with as a transformation in a DTS package but am getting a Error "miss match" on formatdatefor this lineDTSDestination("orderDate") = formatDate(DTSSource("Col002"))I tried the BOL it said use CDate but that does not work either. Am new to the ActiveX...i normally use the copy columnThe reason i want to use this is that my date text is in a string in the source file and i'd like to display it as datetinme in the table my orderdate datatype is DateTime. '**********************************************************************' Visual Basic Transformation Script'************************************************************************' Copy each source column to the destination columnFunction Main() Main = DTSTransformStat_OK DTSDestination("SN") = Trim(DTSSource("Col003")) DTSDestination("orderNumber") = Trim(DTSSource("Col001")) DTSDestination("orderDate") = formatDate(DTSSource("Col002")) DTSDestination("reference") = Trim(DTSSource("Col004")) DTSDestination("country") = "EN" End Function |
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2006-09-20 : 05:36:12
|
| I created this function to over come it as there was no function to work wit.' Use this function to get better control and so you can reuse the code for other datetime columnsFunction formatDate(ByVal Value)' First convert to a date friendly format (yyyy-mm-dd)'Value = Left(Value, 4) + "-" + Mid(Value, 5, 2) + "-" + Mid(Value, 7, 2) US formatValue = Left(Value,2) +"/" +Mid(Value,3,2) + "/"+ Mid(Value,5,4)If IsDate(Value) Then ' Is it a valid date? GetDate = CDate(Value)Else GetDate = Null 'If needed replace with some other appropriate value for invalid datesEnd IfEnd Function |
 |
|
|
|
|
|
ActiveX transformation Error