Convert Date Columns

Converts columns in a DataTable that contain only valid date strings into actual Date type columns.

Only columns where all non-empty values match the formats “yyyy-MM-dd” or “yyyyMMdd” are considered for conversion
Each matching column is replaced with a new column of type Date, populated with parsed values
Empty strings are converted to DBNull
If a value fails parsing during population (unexpected format), it is also set to DBNull
The original column is removed and the new Date column assumes its original name
Conversion is done in-place; the input DataTable is modified directly

    Public Sub ConvertDateColumns(ByRef dt As DataTable)
    
        Dim columnsToConvert As New List(Of String)

        ' Step 1: Identify columns that contain only valid date strings
        For Each col As DataColumn In dt.Columns
            Dim isDateCandidate As Boolean = True

            For Each row As DataRow In dt.Rows
                Dim value As String = row(col.ColumnName).ToString().Trim()

                If value = "" Then Continue For

                Dim parsedDate As Date
                If Not (Date.TryParseExact(value, "yyyy-MM-dd", Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, parsedDate) _
                 OrElse Date.TryParseExact(value, "yyyyMMdd", Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, parsedDate)) Then
                    isDateCandidate = False
                    Exit For
                End If
            Next

            If isDateCandidate Then
                columnsToConvert.Add(col.ColumnName)
            End If
        Next

        ' Step 2: Create new Date columns and replace the original ones
        For Each colName As String In columnsToConvert
            Dim newColName As String = colName & "_date"
            dt.Columns.Add(newColName, GetType(Date))

            For Each row As DataRow In dt.Rows
                Dim value As String = row(colName).ToString().Trim()

                If value = "" Then
                    row(newColName) = DBNull.Value
                Else
                    Dim parsedDate As Date
                    If Date.TryParseExact(value, "yyyy-MM-dd", Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, parsedDate) _
                   OrElse Date.TryParseExact(value, "yyyyMMdd", Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, parsedDate) Then
                        row(newColName) = parsedDate
                    Else
                        row(newColName) = DBNull.Value ' fallback for unexpected format
                    End If
                End If
            Next

            dt.Columns.Remove(colName)
            dt.Columns(newColName).ColumnName = colName
        Next
    End Sub

    Public Sub ConvertDateColumns(ByRef dt As DataTable)
    
        Dim columnsToConvert As New List(Of String)

        ' Step 1: Identify columns that contain only valid date strings
        For Each col As DataColumn In dt.Columns
            Dim isDateCandidate As Boolean = True

            For Each row As DataRow In dt.Rows
                Dim value As String = row(col.ColumnName).ToString().Trim()

                If value = "" Then Continue For

                Dim parsedDate As Date
                If Not (Date.TryParseExact(value, "yyyy-MM-dd", Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, parsedDate) _
                 OrElse Date.TryParseExact(value, "yyyyMMdd", Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, parsedDate)) Then
                    isDateCandidate = False
                    Exit For
                End If
            Next

            If isDateCandidate Then
                columnsToConvert.Add(col.ColumnName)
            End If
        Next

        ' Step 2: Create new Date columns and replace the original ones
        For Each colName As String In columnsToConvert
            Dim newColName As String = colName & "_date"
            dt.Columns.Add(newColName, GetType(Date))

            For Each row As DataRow In dt.Rows
                Dim value As String = row(colName).ToString().Trim()

                If value = "" Then
                    row(newColName) = DBNull.Value
                Else
                    Dim parsedDate As Date
                    If Date.TryParseExact(value, "yyyy-MM-dd", Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, parsedDate) _
                   OrElse Date.TryParseExact(value, "yyyyMMdd", Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, parsedDate) Then
                        row(newColName) = parsedDate
                    Else
                        row(newColName) = DBNull.Value ' fallback for unexpected format
                    End If
                End If
            Next

            dt.Columns.Remove(colName)
            dt.Columns(newColName).ColumnName = colName
        Next
    End Sub

Posted by ipercube