DateTime fields from SQL Server display incorrectly in Excel

Solution 1:

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

Solution 2:

I had the same problem as Andre. There does not seem to be a direct solution, but a CAST in the query that generates the data fixes the problem as long as you can live within the restrictions of SMALLDATETIME. In the following query, the first column does not format correctly in Excel, the second does.

SELECT GETDATE(), CAST(GETDATE() AS SMALLDATETIME)

Perhaps the fractional part of the seconds in DATETIME and DATETIME2 confuses Excel.

Solution 3:

This is a very old post, but I recently encountered the problem and for me the following solved the issue by formatting the SQL as follows,

SELECT CONVERT (varchar, getdate(), 120) AS Date

If you copy the result from SQL Server and paste in Excel then Excel holds the proper formatting.

Solution 4:

Found a solution that doesnt requires to remember and retype the custom datetime format yyyy-mm-dd hh:mm:ss.000

• On a new cell, write either =NOW() or any valid date+time like 5/30/2017 17:35: It will display correctly in your language, e.g. 5/30/2017 5:35:00 PM
• Select the cell, click on the Format Painter icon (the paint brush)
• Now click on the row header of the column that you want to apply the format.

This will copy a proper datetime format to the whole column, making it display correctly.

Solution 5:

I know it is too late to answer to this question. But, I thought it would still be nice to share how I sorted this out when I had the same issue. Here is what I did.

• Before copying the data, select the column in Excel and select 'Format cells' and choose 'Text' and click 'Ok' (So, if your SQL data has the 3rd column as DateTime, then apply this formatting to the 3rd column in excel)
• Now, copy and paste the data from SQL to Excel and it would have the datetime value in the correct format.