Bad Dates 1

[simma]

Hi,
I am importing data from as400 to sql and there are some date 00/00/00 which are creating problem when converting into date type.Can u please advice on how to handle that in vb script in DTS
Rightnow I have

Function Main()
DTSDestination("ClearDate") =cstr(DTSSource("Col010"))
Main = DTSTransformStat_OK
End Function

Thanks

 

[john76]

I have the same problem (dates as '01/01/0001', perfectly acceptable for the iSeries) and have found that the best way for me to handle these dates is a bit of a two-step.

As background, I have set up a linked server to the iSeries via an ODBC System DSN and have called it 'AS400'

1. Format your SQL Query similar to:

SELECT  *
  FROM  OPENQUERY(AS400, 'SELECT  Field1,
                                  Field2,
                                  CASE
                                    WHEN DateField = ''01/01/0001'' THEN NULL [green]-- note the two ' is NOT a single "[/green]
                                    ELSE DateField
                                  END AS DateField,
                                  Field4,...
                         '
        )

Using OPENQUERY passes the query to the AS400 to execute, allowing it to tranform the 01/01/0001 dates into NULLs that SQL Server can handle.

Then your script can look like:

Function Main()
  If IsDate(DTSSource("DateField)) Then
    DTSDestination("DateField") = DTSSource("DateField")
  Else
    DTSDestination("DateField") = [red]<whatever you want to do for this situation>[/red]
End Function

I'm a bit uncertain on the syntax here, I'm not in front of my SQL box so I can't check it, but this should at least be close.

Hope this helps,
John

 

[simma]

Thanks a lot John! seems to be working