Is it possible to substring database fields?

Question

In vb.net, my ultragrid DateTime2 column will not format to show just the date, no matter what code I try.

So, I was wondering, is it possible to use code to get the data from the datetime2 field (called dateStart), and then substring it, splitting it at the space between the date and time, then just display the date part of the string in the ultragrid column?

Code used to fill grid (I'm going to paramterise this after the date problem is solved)

 Public Sub loadIncidents()

    sql = "1=1"
    If ugClients.Selected.Rows.Count = 1 Then
        sql &= " AND clientName = '" & ugClients.Selected.Rows(0).Cells("Account_Code").Text & "'"
    End If

    sql = "SELECT supportID, clientName, dateStart, incidentProblem, incidentSolved FROM dbo.tblIncidents WHERE " & sql & " ORDER BY [supportID] ASC;"

    Dim cm As New OleDbCommand(sql, _con)
    Dim da As New OleDbDataAdapter(cm)
    Dim dt As New DataTable
    da.Fill(dt)
    ugHistory.DataSource = dt

End Sub

Code for InitializeLayout

Private Sub ugHistory_InitializeLayout(sender As Object, ByVal e As Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs) Handles ugHistory.InitializeLayout

    ugHistory.DisplayLayout.Bands(0).Columns("supportID").Header.Caption = "ID"
    ugHistory.DisplayLayout.Bands(0).Columns("clientName").Header.Caption = "Account Code"
    ugHistory.DisplayLayout.Bands(0).Columns("dateStart").Header.Caption = "Date"
    ugHistory.DisplayLayout.Bands(0).Columns("incidentProblem").Header.Caption = "Problem"
    ugHistory.DisplayLayout.Bands(0).Columns("incidentSolved").Header.Caption = "Solved?"

    With e.Layout.Override
        .CellClickAction = CellClickAction.RowSelect
        .AllowAddNew = AllowAddNew.No
        .AllowDelete = DefaultableBoolean.False
        .AllowUpdate = DefaultableBoolean.False
        .AllowColSizing = AllowColSizing.None
        .RowSelectors = DefaultableBoolean.False
    End With

End Sub

I hope this makes sense! Any advice on how to do this would be appreciated

Answer 1

This answer is the best solution. Use SQL Server to copy a column and its contents, drop the original, and voila.

Just bare in mind you'll need to drag the column back into the place of the original, or else saving records will become a problem, with data in the wrong columns, datatype mismatches, etc