1611 - Script Example for Converting Dates

This example assumes a table with two CHAR(12) columns that have been filled with a number of different date formats and non-dates.  Without the script below, dates which don’t convert correctly will cause the record not to be replicated.  The script below sets the value of the field to null to correct for fields with bad dates.

The replication refreshes to a SQL Server table with one column that's a smalldatetime.  Modify this example as needed for your environment. You'll need to repeat the statements between the first objData = recSource.GetValueAfter(1) and the second "End If" following that statement if you have more than two such fields.

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)

            ‘Check to see if our first char(12) field has a legal
            ‘date value and set it to null if it doesn’t
            Dim objData = recSource.GetValueAfter(0)
            If Not objData Is Nothing Then
                Dim strDate = objData.ToString()
                If Not IsDate(strDate) and strDate <> "" Then
                    recSource.SetValueAfter(0, System.DBNull.Value)
                End If

            ‘Check to see if our second char(12) field has a legal
            ‘date value and set it to null if it doesn’t
            objData = recSource.GetValueAfter(1)
            If Not objData Is Nothing Then
                Dim strDate = objData.ToString()
                If Not IsDate(strDate) and strDate <> "" Then
                    recSource.SetValueAfter(1, System.DBNull.Value)
                End If
            End If
        End Sub
    End Class
End Namespace