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?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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:

1. Copy range from SQL Server Management Studio
2. Paste into Excel
3. Select the range of cells that you need formatted as Dates
4. 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

Answer 7

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...

Answer 8

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

Answer 9

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)) = "Double" Then
                If values(r, c) > 1 And Selection(r, c).NumberFormat = "mm:ss.0" Then
                    Selection.Columns(c).NumberFormat = "m/d/yyyy h:mm"
                End If
                Exit For
            ElseIf values(r, c) <> "NULL" Then
                Exit For
            End If
        
        Next
    
    Next

    End Sub

Answer 10

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.

Answer 11

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.