SQL Server 中的 DateTime 字段在 Excel 中显示不正确 [英] DateTime fields from SQL Server display incorrectly in Excel

问题描述

一天中我无数次将记录从 SQL Server Management Studio 复制并粘贴到 Excel.

Countless times during the day I am copying and pasting records from SQL Server Management Studio to Excel.

我的问题是 DateTime 值，例如 8/23/2013 4:51:02 PM 无法正确显示，如下图所示，即使它在功能框中正确显示.

My problem is that a DateTime value such as 8/23/2013 4:51:02 PM does not display correctly as shown in the image below even though it shows correctly in the function box.

将数据类型更改为 ShortDate 可以解决显示问题，但是这样做很乏味，因为我有很多日期字段要格式化.由于 Excel 和 SQL Server 都是 Microsoft 产品，因此人们希望 Excel 能够正确显示日期字段.

Changing the datatype to ShortDate fixes the display issues, but it is tedious to do since I have lots of date fields to format. Since Excel and SQL Server are both Microsoft products one would expect that Excel would be able to correctly show the date field.

所以这似乎是 Excel 的显示问题.我将我的 Management Studio 结果复制到记事本并从那里复制到 Excel 中，但我仍然遇到同样的问题.将 2013-08-23 16:52:11.493 从记事本复制到 Excel 将显示 52:11.5(显示分钟、秒和四舍五入的毫秒).Excel 版本是 2010.

So this appears to be a display issue with Excel. I copied my Management Studio results to Notepad and from there into Excel and I still get the same issue. Copying 2013-08-23 16:52:11.493 from Notepad to Excel will display 52:11.5 (displaying minutes, seconds and rounded milliseconds). Excel version is 2010.

有什么想法可以简化这个吗?

Any ideas to simplify this?

推荐答案

我也遇到了这个问题，只需将 DATETIME 字段从 SQL Management Studio 复制并粘贴到 Excel 中进行操作.Excel 具有正确的 DATETIME 值(即使在粘贴后应用了格式)，但默认情况下没有内置格式来显示 SQL DATETIME.这是修复:

I also had an issue with this problem simply copy and pasting DATETIME fields from SQL Management Studio to Excel for manipulation. Excel has the correct value of the DATETIME (even if the formatting is applied after the paste), but by default doesn't have a built in format to show the SQL DATETIME. Here's the fix:

右键单击单元格，然后选择设置单元格格式.选择自定义.在类型:输入字段中输入

yyyy-mm-dd hh:mm:ss.000

参考:http://office.microsoft.com/en-us/excel-help/create-a-custom-number-format-HP010342372.aspx

这篇关于SQL Server 中的 DateTime 字段在 Excel 中显示不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！