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:
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?