vb.net 更改数据列日期时间格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17412947/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Change DataColumn DateTime Format?
提问by Andrew Morton
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?
我想遍历数据集并将所有 DateTime 列的格式从 SQL Server 返回的默认格式mm/dd/yyyy hh:mm:ss tt（例如 1/1/2011 3:56:50 PM）更改为dd-Mon-yyyy（例如 01-Jan-2011）。这可能吗？
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")?
但我似乎无法为该列指定格式。有没有办法为列分配通用格式，或者我是否必须遍历此循环中的每一行并分配row(column) = row(column).ToString("Format Here")？
Thanks!
谢谢！
回答by Tim Schmelter
DateTimehas no implicit format, it is just a DateTimevalue. You can format it as stringbut then it's a different type.
DateTime没有隐式格式，它只是一个DateTime值。您可以将其格式化为，string但它是一种不同的类型。
For example:
例如：
SELECT REPLACE(convert(varchar, YourDateColumn, 106), ' ', '-')
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?
我正在使用 DataTable 使用 EPPlus 打印出 Excel 工作簿。我想要做的就是在写出工作簿之前更改 DateTime 列中单元格的格式。到目前为止，我只是在做一些错误检查，然后使用 EPPlus 的 LoadFromDataTable 方法将表写入工作簿。我不能只是遍历表格并更改每个 DateTime 列中单元格的字符串格式？
Yes, you can format a column in a sheet created via LoaFromDataTablewith EPPlus, use the appropriate range.Style.Numberformat.Format:
是的，您可以在通过LoaFromDataTableEPPlus创建的工作表中格式化列，使用适当的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.
但是，我更喜欢使用数据库，因为它更有效。
回答by Andrew Morton
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?
您提到为列分配格式 - 如果您使用 DataGridView 来可视化数据，请参阅如何在 DataGridView 中格式化 DateTime 列？
