How do I change the default format for an date time value copied from SSMS to Excel

I often copy the results of a query from Sql Server Management Studio to Excel because I can fiddle with sorting and filtering of results faster using Excel's built in tools than by changing the query I'm running in SSMS. To do this I select everything in the SSMS results grid and just paste it into Excel. For the most part it works correctly; the one exception is that the formatting picked for date time columns isn't suitable and needs to be changed manually afterwards.

My date time values look like: 11/2/2012 3:34:39 PM

Excel defaults to showing 34:39.0 (MM:SS.S)

I want a format that shows the full date and time. I do this by selecting the date time columns, selecting format cells from the context menu and picking one of the formats that show both available under the Number-Date or Number-Time options (normally 3/14/01 13:30; the last option under time).

I'd like to change the default format Excel picks when encountering a time value to that so I don't need to fix it up every time.

Dan is Fiddling by Firelight

Posted 2013-02-14T21:39:52.103

Reputation: 2 677

Is it allowed to pre-format the Excel sheet before you paste your content? Lets say you select all cells and set them as text. Will that fit your needs? Or do you need some automatic column type detection? – nixda – 2013-02-14T21:43:47.103

@nixda No twice over. Firstly: Open Excel, change cell format, paste data isn't any less tedious than open Excel, paste data, change cell format. Secondly, if the data I'm pasting has numeric columns I want those to be treated as numbers for the purpose of sorting not as text. – Dan is Fiddling by Firelight – 2013-02-14T21:47:55.077

Answers

I develop SSMSBoost add-in for SSMS and I have implemented "Export ResultsGrid to Excel" function, that simply scripts ResultsGrid contents as XML Spreadsheet format. (Right click the grid->Script as -> XML Spreadsheet). (Add-in can be used for free.) Data is exported to Excel with preserved datatypes, so Date, Strings and Numbers remain so and can be formatted in excel. This might already help, as far as excel will apply default formatting on date and time and it will be taken from regional settings (at least according to excel manual). Here is description how default formatting is applied and where it can be changed: http://office.microsoft.com/en-us/excel-help/change-the-default-date-time-number-or-measurement-format-HA010351415.aspx#BM2

Andrei Rantsevich

Posted 2013-02-14T21:39:52.103

Reputation: 121

Despite having office in the URL, your second link directs to how to change OS level default formats; the one Excel is defaulting to isn't on that dialog so it's apparently getting it from somewhere else. – Dan is Fiddling by Firelight – 2013-02-15T14:27:52.710

Right, but it states: This setting affects various features in various Microsoft Office programs, including:.... Cells formatted with certain date, time, or currency options in Microsoft Excel – Andrei Rantsevich – 2013-02-15T18:29:19.347

It does not affect the behavior I'm trying to change because Excel isn't using any of the formats listed there. It may be used to produce some of the alternate formats I can change the cells to use; but the default format Excel is picking isn't on that dialog. – Dan is Fiddling by Firelight – 2013-02-15T20:30:32.273