CODEWITHSUNDEEP
.netvb.netdatatable
user1077685
user1077685

Reputation:

Change DataColumn DateTime Format?

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!

Upvotes: 0

Views: 11916

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460058

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.

Upvotes: 2

Andrew Morton
Andrew Morton

Reputation: 25013

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?

Upvotes: 2

Related Questions