SQL Server Portal

Whenever we copy data from SQL Server to excel, we usually face some formatting issues in excel. Specially, if you copy date field from SQL Server to excel. Given below is the script and screen image.

USE AdventureWorks2012
GO
SELECT * FROM [HumanResources].[Department]
GO
--Copy the result set of the above query and paste into excel.

Let me explain the solution step by step.

Step 1 :
Execute the query and paste the data from result set to excel.

USE AdventureWorks2012
GO
SELECT * FROM [HumanResources].[Department]
GO
--Copy the result set of the above query and paste into excel.

Step 2 :
Once you paste the data in excel, just select the date column field, right click on it and select format cells.. from the pop up menu as shown in the picture.

Step 3 :
Once you are in the format cells.. dialogue box, you can select the category as a date and whatever type you want you can select. Given below is the screen image.

Step 4 :
Press OK once you are done and your date column will be formatted as shown in the picture below.

Note : I used SQL Server 2005 & Excel 2007 to demonstrate this problem and its solution.

Let me know if you know a better solution.