How to change the format from DataTable and Show in DataGridView in vb.net

Question

How to change the format of DataTable and Show in DataGridView in vb.net?. I want to change the format of the DataTable without going through the DataGridView format and displaying in the DataGriView. Please recommend the best solution. I tried but the result is still in the datagridview has not changed as I attached in the screenshot below.

Thanks

        Dim dt As New DataTable("tbl")
        Dim dtColumn As DataColumn
        dtColumn = New DataColumn()
        dtColumn.DataType = GetType(DateTime)
        dtColumn.ColumnName = "CreatedDate"
        dt.Columns.Add(dtColumn)
        Dim dr As DataRow = dt.NewRow()
        dr("CreatedDate") = DateTime.Now
        Dim dr1 As DataRow = dt.NewRow()
        dr1("CreatedDate") = DateTime.Now.AddDays(1)
        dt.Rows.Add(dr)
        dt.Rows.Add(dr1)
Dim query = dt.AsEnumerable().Select(Function(r) r.Field(Of DateTime)("CreatedDate").ToString("yyyy/MM/dd"))
DataGridView1.DataSource = dt
'if I run the console then the output results change this should be possible without formatting the datagridview
        'For Each r In query
        '    Console.WriteLine(r.ToString())
        '    Console.WriteLine("Press any key to continue")
        '    Console.ReadKey()

        'Next r

Answer 1

You can remove .ToString("yyyy/MM/dd").

In your form designer, select your DataGridView and browse to events. Add an eventhandler for ColumnAdded.

This will generate the following code:

Private Sub DataGridView1_ColumnAdded(sender As Object, e As DataGridViewColumnEventArgs) Handles dataGridView1.ColumnAdded

End Sub

Now you can define what the event handler has to do whenever a columns is being added to your datagridview, so:

Private Sub DataGridView1_ColumnAdded(sender As Object, e As DataGridViewColumnEventArgs) Handles dataGridView1.ColumnAdded

    ' Cast the argument to DataGridViewTextBoxColumn
    Dim c As DataGridViewTextBoxColumn = TryCast(e.Column, DataGridViewTextBoxColumn)

    ' Test if conversion has been successful
    If c IsNot Nothing Then

        Select Case c.ValueType

            Case GetType(Date) ' If ValueType of column being added is Date

                Dim cellStype As New DataGridViewCellStyle With {.Format = "dd/MM/yyyy"} ' Set here the format for this DataGridViewTextBoxColumn
                c.DefaultCellStyle = cellStype ' Apply the style

                ' Add here other ValueType you want to format differently, if any

        End Select

    End If

End Sub

Note that you could define the format for others ValueType simply adding the proper case in the same event handler.

Result:

I highly recommend you to enable Option Strict On for your VB.Net projects. You can read more about it here.

--- Update ---

As per your comment, what you really need is simply changing from DateTime (Date) type to DateOnly.

Here's your code updated accordingly, please note I simplified object initializations and changed the way you refer to the specified DataColumn - if you can, it's preferrable using the DataColumn object itself instead of its name (a string).

Private Sub ImprovedCode()

    Dim dt As New DataTable("tbl")

    ' Change DateTime to DateOnly
    Dim dtColumn As New DataColumn With {.DataType = GetType(DateOnly), .ColumnName = "CreatedDate"}
    dt.Columns.Add(dtColumn)

    Dim dr As DataRow = dt.NewRow()
    dr(dtColumn) = DateOnly.FromDateTime(Date.Now) ' This is instead of dr("CreatedDate")

    Dim dr1 As DataRow = dt.NewRow()
    dr1(dtColumn) = DateOnly.FromDateTime(Date.Now) ' This is instead of dr("CreatedDate")
    dt.Rows.Add(dr)
    dt.Rows.Add(dr1)
    dataGridView1.DataSource = dt

End Sub

Result from Visual Studio DataTable visualizer:

Please note: DateOnly and TimeOnly are available only in .Net 6 and higher - if your project targets .Net Framework, you can not use these types. By the way, if your project is targeting .Net Framework, I suggest you to read this answer on SO about which .Net version choose.

Update As the question owner commented below, there's a library which enables DateOnly in unsupported frameworks: Portable.System.DateTimeOnly