2021-10-12
During a coffee break at a recent Learning Tree course, Excel Data Analysis Training, June C. commented that when she pasted results from the SQL Server Management Studio into Excel "weird things happened." Well, June, it's actually not weird, but I definitely know what you are talking about.
Microsoft Excel and Microsoft SQL Server have datatypes that are similar, but definitely identical. When you import data into Excel from SQL Server, whether you are using ODBC or SQL Native Client, the underlying library of data import code generally does all the right things and the data seems fine. However, when you copy and paste, you are relying on Excel to correctly interpret the pasted data. There is no data access code behind the scenes to fix things up for you.
Datetime data is particularly sensitive because both SQL Server and Excel must constantly convert back and forth between their internal datetime data and the familiar formatted dates that we humans are used to.
Let's examine what happens when we paste data into an Excel workbook. To investigate fully, we will want to see how Excel interprets the pasted datatype. VBA has a function that will tell us the datatype of a variable; to use this function on a worksheet cell we will have to create a simple "wrapper" function.
Function VarTypeTest(v As Variant) As String
' a wrapper for VarType so it can be used from worksheets
Select Case VarType(v)
Case vbArray
VarTypeTest = "vbArray"
Case vbBoolean
VarTypeTest = "Boolean"
Case vbByte
VarTypeTest = "Byte"
Case vbCurrency
VarTypeTest = "Currency"
Case vbDataObject
VarTypeTest = "DataObject"
Case vbDate
VarTypeTest = "Date"
Case vbDecimal
VarTypeTest = "Decimal"
Case vbDouble
VarTypeTest = "Double"
Case vbEmpty
VarTypeTest = "vbEmpty"
Case vbLong
VarTypeTest = "Long"
'Case vbLongLong '64-bit
'VarTypeTest = "LongLong"
Case vbNull
VarTypeTest = "Null"
Case vbObject
VarTypeTest = "Object"
Case vbSingle
VarTypeTest = ""
Case vbString
VarTypeTest = "String"
Case vbUserDefinedType
VarTypeTest = "User Defined Type"
Case vbVariant
VarTypeTest = "Variant"
End Select
End Function
This function will return a string describing how Excel has interpreted the datatype of the data pasted from the Management Studio.
Of course, we'll need some data to paste.
SELECT GETDATE() AS Datetime
, CAST(GETDATE() AS SMALLDATETIME) AS SmallDatetime
, CAST(GETDATE() AS DATETIME2) AS [Datetime2]
, SYSDATETIMEOFFSET() AS [DateTimeOffset]
, CONVERT(CHAR, GETDATE(), 100) AS [Char Style 100]
, CONVERT(CHAR, GETDATE(), 101) AS [Char Style 101]
, CONVERT(CHAR, GETDATE(), 106) AS [Char Style 104]
, CONVERT(CHAR, GETDATE(), 109) AS [Char Style 109]
Here is the resultset in the Management Studio:
When we copy and paste, the results look like this:
The Sql Server datetime and datetime2 datatypes look odd, but are actually OK. Excel correctly interprets the data as datetime data, but applies a custom format.
If we manually change the formatting to a date format of our liking, we see that the format changes for some, but not all of the "dates". Similarly, if we attempt to add a day to each of the "dates" we fail for some. To appreciate the reason for this, we apply the VarTypeTest macro function to the data imported from Excel. We observe that some SQL Server output datatypes and formats are interpreted as dates, two are stored as doubles in Excel, but some SQL Server datatypes (shown in red) are not recognized as dates at all and are stored in Excel as string data. These cells will not accept a different datetime format and cannot be used as dates in calculations.
Note that formatted outputs from the CONVERT function in SQL are most definitely character data when they leave SQL Some formats can be correctly interpreted by Excel as dates, others not.
One Last Peculiarity
It seems so obvious that all the dates in this example should be the same, since they all (save one) originate by calling the GETDATE( ) function. But in fact, something interesting is going on behind the scenes. Although the SQL Server datetime and smalldatetime datatypes and the Excel date datatype store a date as the number of days since January 1, 1900, they do not do this calculation in that same way. Therefore, even though Excel seems to be treating the datetime data as a double datatype, the actual numerical value must have been adjusted during the paste operation to have the Excel value, not the SQL Server value. Let's try one more experiment.
We will get today's date from SQL Server using the Management Studio, and we will also convert this date to a float datatype, the equivalent of Excel's double datatype.
SELECT GETDATE() AS Datetime
, CAST(GETDATE() AS FLOAT) AS Float
When we paste this data into Excel, we get a surprise:
The values for the same date are off by two! Part of this inconsistency is easy to explain. SQL Server treats January 1, 1900 as day 0, while Excel treats it as day 1. That explains a difference of one, but not two.
The reason for the second day of difference is far, far odder. Let's use the Excel date format tools to look at the day of the week for February 29, 1900.
Looks like February 29, 1900 was a Wednesday. On small problem, though. There was no February 29, 1900. 1900 was not a leap year. When Excel was first introduced many years ago, Lotus 1-2-3 dominated the spreadsheet market. The developers of Lotus took a shortcut in date calculation, and for most applications erroneously treating 1900 as a leap year made no difference. Microsoft, just entering the market, decided that compatibility with Lotus was critically important for success in the marketplace, and the Excel developers intentionally included this error in their code.
Conclusion
I have always been a fan of the ease of copy-and-paste to move small datasets from the Management Studio into Excel. But when the datasets include datetime data, care must be taken. It is often helpful to use the CAST function in SQL statements to convert to the SMALLDATETIME datatype, which is correctly recognized and formatted by Excel. If you use CONVERT to output a character representation of a date, make certain that Excel recognizes the format as a date and not as a string.
