Tuesday, February 08, 2005

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

5 comments:

wOOdy said...

This is again a typical example on the weakness of DotNet in reallife scenarios. It is surely a great language, a superior methology and all, but it stil has not all the bells and wistles of a mature language like VFP.

Nobody in the VFP world would save a Time as a Char (that's why there is a vartype of DateTime) and to see that convulted code just to parse and reconstruct the values, I just think: Thankfully I'm using VFP!

Beth Massi said...

Woody,

The fact that we are storing time values separate from the date has nothing to do with the language. We store them that way in our VFP products as well.

-B

Ryan said...

One good example of needing to separate Time into a separate column:

If I need to collect a date/time, but the time portion is optional, how would I represent that in a single date/time column? I can't, whether I'm using ADO.NET or VFP.

wOOdy said...

Hi Ryan, Of course you can. If the Time portion of a date is "optional", it's stil a DateTime with that part set to 00:00:00. Where's the problem?

Beth Massi said...

Yes the time portion is set to 12:00:00 AM in the database so how do you know if the user really entered 12am or if they mean that no time is specified?