DateTime fields from SQL Server display incorrectly in Excel

excel sql-server-2008-r2
Andre picture Andre · Sep 3, 2013 · Viewed 110.9k times · Source

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

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.

enter image description here

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.

EDIT: 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?

Answer

user2688162 picture user2688162 · Apr 30, 2014

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:

Right click the cell, and choose Format Cells. Choose Custom. In the Type: input field enter

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

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

Related questions

SQL Server 2008 R2 Excel Import

I'm using SS 2008 R2 and Office 2010 on a Windows 7 64 bit. I am trying to use the import wizard to import an excel sheet into a table. I get this error: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More