Menu
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.
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?
289
To change the date or time format, right-click on a cell, and select Format Cells. Then, on the Format Cells dialog box, in the Number tab, under Category, click Date or Time and in the Type list, select a type, and click OK.
On the Home tab, in the Number group, click the Dialog Box Launcher next to Number. You can also press CTRL+1 to open the Format Cells dialog box. In the Category list, click Date or Time. In the Type list, click the date or time format that you want to use.
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
158
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
