Problem pasting datetimes from SSMS into Excel
-
If you run a query in SSMS that contains datetime columns (with fractions of a second) then paste the results into Excel, it doesn't format the time correctly.
For example, paste the following into Excel:
2019-07-04 16:55:34.123
2019-07-04 16:55:34
The results are:
Excel adds an extra space between the date and the time parts of the string, if the string contains fractions of a second, so the text is actually pasted as: 2019-07-04 16:55:34.123.
This has been annoying me for several years, the way I have been getting around it is to select the column(s) in Excel then Format Cells, Custom, dd/mm/yyyy hh:mm:ss.000 which fixes the format. But if you have some queries the have multiple datetime columns it becomes a bit of a chore.
Has anyone got a better solution without having to format cells or change the query?
Is there a setting that can be changed in Excel so it accepts the pasting of datetimes with fractions of a second and correctly formats them?
-
This is obviously not the right place to ask about settings in Excel. However, what I do when I don't want Excel to munch the data, for instance drop leading zeroes in string is to run the query from Excel. The Data tab on the ribbon, and then select New Query, From Database and then SQL Server. You want the Advanced Options, so that you can submit a query.
I cannot vouch for that all data types are handled properly (Excel is after all Excel), but at least Excel now gets information about the data type from SQL Server. (Which does not happen when you copy and paste.)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
-
I think all you need to do is change how it is formatting the cell. When I paste that in with a straight paste (no paste special or anything like that), it pastes it in as a "Custom" type. After pasting it in, change the custom type from:
mm:ss.0
to
dd/mm/yyyy mm:ss.000
and you should be getting what you want.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply