active x question

[swoodring]

I am new to visual basic so this might seem like a simple question. I am trying to take a date field and a time field from an as/400 and put it into a smalldatetime field. When I try to use an activex using just the date field or just the time field I get the correct date or time. But when I try combining them I do not get what I'm expecting the time looks correct but the date is way off. Any ideas would be greatly appreciated. I've included the code I've been using.


Dsrddati = 2001-01-28
Dsrdtime = 10.49.00

Time by itself
Function Main()
DTSDestination("TransactionDate&quot = cdate(DTSSource("dsrdtime&quot)
Main = DTSTransformStat_OK
End Function
result = 2/14/01 10:49:00 AM



Date by itself
Function Main()
DTSDestination("TransactionDate&quot = cdate(DTSSource("dsrddati&quot)
Main = DTSTransformStat_OK
End Function
result = 1/28/01


Combining both date and time
Function Main()
DTSDestination("TransactionDate&quot = cdate(DTSSource("dsrddati&quot) + cdate(DTSSource("dsrdtime&quot)
Main = DTSTransformStat_OK
End Function
result = 3/16/2102 10:49:00 AM

 

[foxdev]

Try using "&" instead of "+" to concatenate the strings first and use a single CDate call. Robert Bradley

http://www.foxdev.com/

Do you have too much money? Visit

http://www.agrainofhope.org

 

[swoodring]

when I do the following I get an error stating type mismatch cdate.

Function Main()
DTSDestination("TransactionDate&quot = cdate((DTSSource("dsrddati&quot & DTSSource("dsrdtime&quot))
Main = DTSTransformStat_OK
End Function


If I just do the concatation part I get the following result.
Function Main()
DTSDestination("TransactionDate&quot = (DTSSource("dsrddati&quot & DTSSource("dsrdtime&quot)
Main = DTSTransformStat_OK
End Function
result = 1/28/0112/14/01 10:49:00 AM.

any other suggestions?

 

[foxdev]

Well, you tried what I had in mind. In such cases (when DTS is my only option), I result to the following:

* import each (date, time) into separate columns in a staging table (i.e. columns datetemp, timetemp)

* concatenate them in the insert into the destination, such as:

[tt]insert into MyFinalTable
select col1, col2,..., cast(datetemp + ' ' + timetemp)
from MyStagingTable [tt]
Robert Bradley

http://www.foxdev.com/

Do you have too much money? Visit

http://www.agrainofhope.org

 

[foxdev]

Sorry - that middle line of code should be:

[tt]select col1, col2,..., cast(datetemp + ' ' + timetemp as smalldatetime) [/tt] Robert Bradley

http://www.foxdev.com/

Do you have too much money? Visit

http://www.agrainofhope.org

 

[swoodring]

Actually just tried this and this seems to work. Thanks for your help. Stacy

Function Main()
DTSDestination("TransactionDate&quot = cdate(DTSSource("dsrddati&quot) & " " & timevalue(DTSSource("dsrdtime&quot)
Main = DTSTransformStat_OK
End Function