Convertring Invalid Dates From Oracle

[abombss]

I am connecting to an Oracle 7 server and trying to use DTS to store the data in my sql server. In the Oracle database dates which should be null are stored as 0001-01-01 obvisouly I get an error if I try and take this date and store it directly into SQL. I wrote an ActiveX transformation in vbscript which looks something like this to fix the date.

Function Main()
DTSDestination("TEST_DTE&quot = FixDate(DTSSource("DTE_ENT&quot)

End Main

Function FixDate(varDate)
'Truncate the time, just show date
varDate = Left(CStr(varDate),10)
If varDate = "0001-01-01" Then varDate = NULL
FixDate = varDate
End FixDate

When I run this I get an error on the line 1 of Main() which says Error: Type Mismatch on DTSSource. When I look at the data type of the source column it is a datetime type. How can I change the source type? Or how can I fix this problem?

I was thinking about using TO_CHAR(DTE_ENT) in my select statement in my source, but I would prefer to not use this method. I would rather write an ActiveX Script.

If anyone has any insights or additional help or feedback I would much appreciate it.

Thanks,

abombss
abombss@hotmail.com

 

[Tim1]

Hiya,

First suggestion is have you posted this into the Oracle forum?? I am not that familiar with Oracle, but I do know that it has a convert function to deal with just the kind of problems that you are facing, but I don't know the syntax.

Tim