In the #sqlhelp Slack channel on the SQL Server Community Slack workspace last month, Jemma Hooper asked:
When trying to
binary(8), I’m getting a “binary or text data would be truncated” error. This seems really weird in context with the fact that
SELECT DATALENGTH()on a
DATETIME2value returns 8 (i.e., 8 bytes) as the result. This seems to be consistent across multiple versions of SQL Server. Has anyone come across this before? Cast to
Sample scripts:SELECT CAST(SYSDATETIME() AS BINARY(8)) -- returns "Msg 8152, Level 16, State 17, Line 1 -- String or binary data would be truncated." SELECT CAST(SYSDATETIME() AS BINARY(9)) -- returns valid binary value SELECT DATA_LENGTH(SYSDATETIME()) -- returns 8
Quite a lot to take in. Let’s break this down.
DATETIME2 is a data type that was introduced in SQL Server 2008. It uses up to 8 bytes to store a date and time: 3 bytes for the date component, and up to 5 bytes for the time component.
The point here is that it uses 8 bytes in total. That’s it. No more.
Jemma noted that when converting the
DATETIME2 data type to
BINARY, it suddenly became longer by exactly one byte, which seems strange.
My fine readers will remember that binary is rendered as hexadecimal code when we look at it in SQL Server Management Studio, which means that a byte is represented by two hexadecimal characters stuck together.
Let’s use an example. I’m going to create a
DATETIME2(7) variable with today’s date and time, using the built-in
DECLARE @dt DATETIME2(7) = SYSUTCDATETIME(); SELECT @dt;
Our result looks like this:
To show how SQL Server stores it, let’s convert it to binary and display the output. Taking Jemma’s findings into account, we’ll skip the error and jump to
VARBINARY(25) for safety:
SELECT CAST(@dt AS VARBINARY(25));
As expected, this is the result, in binary. Notice that it is nine bytes long, as Jemma pointed out (I’ve expanded it for legibility):
07 F7 AF 30 59 4B 5D 3D 0B
I suspected that it had something to do with the variable
TIME portion of the data type, so I split the value into respective
TIME in binary, to confirm that the extra byte was in the
TIME component. This required some creativity, to cast the full value to a component, and then to binary.
SELECT CAST(CAST(@dt AS DATE) AS VARBINARY(25)); SELECT CAST(CAST(@dt AS TIME(7)) AS VARBINARY(25));
Sure enough, the extra byte is in the
0x5D3D0B (3 bytes)
0x07F7AF30594B (6 bytes)
Notice when comparing these results to the full
DATETIME2(7) value above, that the date is stored to the right of the time value when it’s in binary format. This is likely something to do with the way SQL Server persists data to disk in Little Endian (byte-reversed) format. To SQL Server, the date is first (reading right to left), then the time, then the mystery
0x07 at the end.
To be precise
While we were both trying to figure out this extra byte, I noticed that the binary value always seemed to start with a
0x07. But when I converted to
0x07 became a
Jemma figured it out a few seconds before I did: the leading byte (the
0x07) is the precision of the
TIME data type.
The reason that a
TIME) data type is one byte longer when converted to a binary value is because the precision is encoded directly into the value. This is to ensure no information is lost when converting between data formats.
Feel free to share your cool findings about data type conversions on Twitter, at @bornsql.
One of the best post I read recently. Thank you so much!
That is high praise coming from your lovely self, Pinal. Thank you.