ADO.NET Calculated DateTime Expression

In our business we store user entered time values as strings separate from the date. If you're doing the same thing, here's an example of how you can create a calculated column in a DataTable that merges the date and time so you can use it in your business functions a lot easier. This will work the same regardless of whether you're storing the time value as military time or AM/PM and handles null values.

Module Module1
 Sub Main()
   
   Dim dt As New DataTable
   
   dt.Columns.Add(New DataColumn("MyDate", GetType(Date)))
   dt.Columns.Add(New DataColumn("MyTime", GetType(String)))

   Dim expr As String = _
   "SUBSTRING(CONVERT(MyDate, 'System.String'),1,LEN(CONVERT(MyDate, 'System.String'))-11) + ' ' + ISNULL(MyTime,'')"

   dt.Columns.Add(New DataColumn("MyDateTime", GetType(Date), expr))

   For i As Integer = 1 To 10
      Dim dr As DataRow = dt.NewRow
      
      If i < 5 Then
         dr(0) = DateAdd(DateInterval.Day, i, Now)
         dr(1) = i.ToString + ":00 PM"
      Else
         dr(0) = DateAdd(DateInterval.Day, 0, Now)
         dr(1) = i.ToString + ":00 AM"
      End If
      
      dt.Rows.Add(dr)
   Next

   Dim dv As New DataView(dt)
   dv.Sort = "MyDateTime"
   
   Console.WriteLine(ViewToString(dv))
   Console.ReadLine()

 End Sub

 Public Function ViewToString(ByVal view As DataView) As String
   Dim sb As New Text.StringBuilder

   For i As Integer = 0 To view.Count - 1
      Dim row As DataRow = view(i).Row
      sb.Append("Row ")
      sb.Append(i.ToString)
      sb.Append(": ")
      sb.Append(RowToString(row))
   Next
   Return sb.ToString
 End Function

 Public Function RowToString(ByVal row As DataRow) As String
   Dim sb As New Text.StringBuilder
   sb.Append(vbCrLf)

   For Each dc As DataColumn In row.Table.Columns
      If Not row.RowState = DataRowState.Deleted Then
         sb.Append(StrDup(10, " "))
         sb.Append(LSet(dc.ColumnName, 20))
         sb.Append(" : ")
         If row(dc) Is System.DBNull.Value Then
            sb.Append("<null>")
         Else
            sb.Append(row(dc).ToString)
         End If
         sb.Append(vbCrLf)
      End If
   Next
   Return sb.ToString
 End Function
End Module