Change DataColumn DateTime Format?

Question

I'd like to loop through a data set and change the format of all DateTime columns from the default format returned by SQL Server mm/dd/yyyy hh:mm:ss tt (e.g. 1/1/2011 3:56:50 PM) to dd-Mon-yyyy (e.g. 01-Jan-2011). Is this possible?

I can access the datatables for each table in the dataset via the following code block:

Using ds As DataSet = _bo.getHistoryDataSet(projectId)

    For Each dt As DataTable In ds.Tables

        For Each col As DataColumn In dt.Columns
            If col.DataType Is GetType(DateTime) Then
               //Format column here?
            End If
        Next
    Next

End Using

But I can't seem to be able to assign a format to the column. Is there a way to assign a general format to the column or do I have to loop through each row inside this loop and assign row(column) = row(column).ToString("Format Here")?

Thanks!

`DateTime` ***does not have a format***; a `DateTime` *is just a number* - with some semantics around it. Concepts like "format" apply at the UI when *displaying* the `DateTime`, but do not apply to the `DateTime` itself. This is a bit like saying "I have a boolean property; how do I make that use italics?" — Marc Gravell, Jul 01 '13 at 20:21
The `ToString()` format would simply be `"dd-MMM-yyyy"`. But, as said by others, the format is used to display the date. You cannot set the format for a column within a DataTable. — JDB, Jul 01 '13 at 20:36
I'm using the DataTable to print out an Excel workbook using EPPlus. All I want to do is change the format of the cells in DateTime columns before writing the workbook out. As of now, I'm just doing some error checking then writing the table to a workbook using EPPlus's LoadFromDataTable method. I can't just loop through the table and change the String format of the cells in each DateTime column? — , Jul 01 '13 at 20:41

Tim Schmelter · Accepted Answer · 2013-07-01T20:48:02.810

DateTime has no implicit format, it is just a DateTime value. You can format it as string but then it's a different type.

For example:

SELECT REPLACE(convert(varchar, YourDateColumn, 106), ' ', '-')

How to format datetime & date

From your comment on your question:

I'm using the DataTable to print out an Excel workbook using EPPlus. All I want to do is change the format of the cells in DateTime columns before writing the workbook out. As of now, I'm just doing some error checking then writing the table to a workbook using EPPlus's LoadFromDataTable method. I can't just loop through the table and change the String format of the cells in each DateTime column?

Yes, you can format a column in a sheet created via LoaFromDataTable with EPPlus, use the appropriate range.Style.Numberformat.Format:

For Each col As DataColumn In tblExcel.Columns
    If col.DataType = GetType(Date) Then
        Dim colNumber = col.Ordinal + 1
        Dim range = workSheet.Column(colNumber) 
        range.Style.Numberformat.Format = "dd-Mon-yyyy" 
    End If
Next

However, i would prefer using the database because it's more efficient.

Thank you for your answer. I actually asked this question and used similar SQL to format the dates, but a friend told me that I should just feed raw data from the SQL and format it at the visual layer (in this case, .NET). I am trying to stick to best practices, so I'd like to do that if possible. — , Jul 01 '13 at 20:44
@TimeBomb006: Using the database is always the most efficient approach. However, if you would use the table elsewhere, not only for `LoadFromDataTable` i would export the `DateTime` values and convert/format it at the very last place. Edited my answer to show how you can format an already created excel sheet. In this example `tblExcel` is a DataTable used for `LoadFromDataTable` and `workSheet` is the current worksheet where you want to apply the new format. — Tim Schmelter, Jul 01 '13 at 20:50

score 2 · Answer 2 · edited May 23 '17 at 12:11

2

You mention assigning a format to the column - if you're using a DataGridView to visualise the data then please see How to format DateTime columns in DataGridView?

edited May 23 '17 at 12:11

Community

1
1

answered Jul 01 '13 at 20:36

Andrew Morton

24,203
9
60
84

Change DataColumn DateTime Format?

2 Answers2