DateTime fields from SQL Server display incorrectly in Excel

Question

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

User · Answer

This is a very old post  but I recently encountered the problem and for me the following solved the issue by formatting the SQL as follows    SELECT CONVERT  varchar  getdate    120  AS Date  If you copy the result from SQL Server and paste in Excel then Excel holds the proper formatting

User · Answer

Try the following  Paste  2004-06-01 00 00 00 000  into Excel   Now try paste  2004-06-01 00 00 00  into Excel   Excel doesn t seem to be able to handle milliseconds when pasting

User · Answer

Here s a simple macro that can be run after pasting data from SSMS  It s easiest if you copy it to your PERSONAL XLSB file and add a button to the Quick Access Toolbar or a new custom group tab in the ribbon  Run the macro immediately after pasting while the data is still selected  It can also be run if a single cell is selected within the data - it will automatically select the current area before running  same as ctrl-a   To run the macro on only a subset of data  select the desired subset before running  It can handle the data including or excluding headers  but assumes there at at least 2 rows in the current area  It efficiently tests each column to see if the first non-NULL value looks to be a date time value in the strange format  If it is  it sets the entire column to the default system date time format  even if your date format is  d m y   Sub FixSSMSDateFormats     Intended for copied data from SSMS and handles headers included  For selection or current area  checks each column        If the first non-NULL value is in strange time format  then change entire column to system date time format  Dim values As Variant  r As Long  c As Long  If Selection Count   1 Then Selection CurrentRegion Select  values   Selection Value  For c   1 To UBound values  2       For r   2 To UBound values  1               If TypeName values r  c      quot Double quot  Then             If values r  c   gt  1 And Selection r  c  NumberFormat    quot mm ss 0 quot  Then                 Selection Columns c  NumberFormat    quot m d yyyy h mm quot              End If             Exit For         ElseIf values r  c   lt  gt   quot NULL quot  Then             Exit For         End If          Next  Next  End Sub

User · Answer

Here s a hack which might be helpful    it puts an apostrophe in front of the time value  so when you right-click on the output in SSMS and say  Copy with Headers   then paste into Excel  it preserves the milliseconds   nanoseconds for datetime2 values   It s a bit ugly that it puts the apostrophe there  but it s better than the frustration of dealing with Excel doing unwanted rounding on the time value   The date is a UK format but you can look at the CONVERT function page in MSDN   SELECT      CONVERT VARCHAR 23   sm MilestoneDate  103  AS MilestoneDate             CONVERT VARCHAR 23   sm MilestoneDate  114  AS MilestoneTime    FROM SomeTable sm

User · Answer

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

User · Answer

i ve faced the same problem when copying data from ssms to excel  the date format got messed up  at last i changed my laptop s system date format to yyyy-mm-dd from yyyy mm dd  everything works just fine

User · Answer

Although not a complete answer to your question  there are shortcut keys in Excel to change the formatting of the selected cell s  to either Date or Time  unfortunately  I haven t found one for Date Time    So  if you re just looking for dates  you can perform the following    Copy range from SQL Server Management Studio Paste into Excel Select the range of cells that you need formatted as Dates Press Ctrl Shift 3   For formatting as Times  use Ctrl Shift 2   You can use this in SQL SERVER   SELECT CONVERT nvarchar 19  ColumnName 121  AS  Changed On  FROM Table

User · Answer

I had the same problem as Andre  There does not seem to be a direct solution  but a CAST in the query that generates the data fixes the problem as long as you can live within the restrictions of SMALLDATETIME  In the following query  the first column does not format correctly in Excel  the second does   SELECT GETDATE    CAST GETDATE   AS SMALLDATETIME   Perhaps the fractional part of the seconds in DATETIME and DATETIME2 confuses Excel

User · Answer

I know it is too late to answer to this question  But  I thought it would still be nice to share how I sorted this out when I had the same issue  Here is what I did    Before copying the data  select the column in Excel and select  Format cells  and choose  Text  and click  Ok   So  if your SQL data has the 3rd column as DateTime  then apply this formatting to the 3rd column in excel   Now  copy and paste the data from SQL to Excel and it would have the datetime value in the correct format

User · Answer

Found a solution that doesnt requires to remember and retype the custom datetime format yyyy-mm-dd hh mm ss 000    On a new cell  write either  NOW   or any valid date time like 5 30 2017 17 35  It will display correctly in your language  e g  5 30 2017  5 35 00 PM Select the cell  click on the Format Painter icon  the paint brush  Now click on the row header of the column that you want to apply the format     This will copy a proper datetime format to the whole column  making it display correctly

User · Answer

I ve had this same problem for a while as I generate a fair number of ad-hoc reports from SQL Server and copy paste them into Excel  I would also welcome a proper solution but my temporary workaround was to create a default macro in Excel which converts highlighted cells to Excel s datetime format  and assigned it to a hotkey  Shift-Ctrl-D in my case   So I open Excel  copy paste from SSMS into a new Excel worksheet  highlight the column to convert and press Shift-Ctrl-D   Job done

[excel] DateTime fields from SQL Server display incorrectly in Excel

Examples related to excel

Examples related to sql-server-2008-r2