Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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 Feb 14 '13 at 21:43
    
@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 Neely Feb 14 '13 at 21:47

1 Answer 1

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

share|improve this answer
    
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 Neely Feb 15 '13 at 14:27
    
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 Feb 15 '13 at 18:29
    
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 Neely Feb 15 '13 at 20:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.