1610 - Script Example of Data to Datetime Conversion

This script example assumes that you are replicating from IBM Db2 for i/iSeries/AS400 to Microsoft SQL Server, and you have an 8 digit Date field with the value in format YYYYMMDD. The script converts dates in this format to SQL Server Datetime format.

If you use a different mapping you'll need to change the expressions that set the values for strYear, strMonth and strDay in the replication script.

The global script below sets up a global variable to store the value of the decimal date from the IBM i, before setting the field to Null. This avoids a conversion error in the replication script.  

Global Script

Imports System
Imports System.Data
Imports Microsoft.VisualBasic
Imports DBMotoPublic
Imports DBMotoScript
Imports DBRS.GlobalScript
 
Namespace DBRS
    Public Class GlobalScript : Inherits IGlobalScript
 
      ' Declare the variables used to store the value before
      ' setting it to null to avoid the conversion error
      Public Shared strDecDate as String
    End Class
End Namespace

Replication Script

The replication script stores the original Date value as a string and sets the field to Null in the onBeforeMapping event, and then changes the value after mapping in the onAfterMapping event.

Imports System
Imports System.Data
Imports Microsoft.VisualBasic
Imports DBMotoPublic
Imports DBMotoScript
Imports DBRS.GlobalScript
 
Namespace DBRS
    Public Class ReplicationScript : Inherits IReplicationScript
        Public Overrides Sub Record_onBeforeMapping(recSource As IRecord, ByRef AbortRecord As Boolean)
            Dim objDecDate as object
           
            ' Save the decimal value of the date from the AS/400 to use after mapping
            objDecDate = recSource.GetValueAfter("DECDATE")
            strDecDate = objDecDate.ToString()
           
            ' Set the value of the decimal date to null to avoid the conversion error
            recSource.SetValueAfter("DECDATE", System.DBNull.Value)
        End Sub
 
        Public Overrides Sub Record_onAfterMapping(recSource As IRecord, recTarget As IRecord, ByRef AbortRecord As Boolean)
            Dim strYear as String
            Dim strMonth as String
            Dim strDay as String
           
            strYear = Mid(strDecDate, 3, 4)
            strMonth = Mid(strDecDate, 7, 2)
            strDay = Mid(strDecDate, 9, 2)
           
            ' Set the value in the target field after converting it
            ' from the string representation of our decimal value
            recTarget.SetValueAfter("actdate", CDate(strMonth & "/" & strDay & "/" & strYear))
        End Sub
    End Class
End Namespace