Why is a value in DATETIME2 8 bytes, but in BINARY it is 9 bytes?

In the #sqlhelp Slack channel on the SQL Server Community Slack workspace last month, Jemma Hooper asked:

When trying to CAST or CONVERT a datetime2 value 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 DATETIME2 value 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 BINARY(9) works fine.

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.

Testing, testing

Let’s use an example. I’m going to create a DATETIME2(7) variable with today’s date and time, using the built-in SYSUTCDATETIME() function.

DECLARE @dt DATETIME2(7) = SYSUTCDATETIME();
SELECT @dt;

Our result looks like this:

2017-10-04 08:59:21.8910199

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

Actual result: 0x07F7AF30594B5D3D0B
Readable result: 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 DATE and 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 TIME component:

Date result: 0x5D3D0B (3 bytes)
Time result: 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 TIME(6) and TIME(5), the 0x07 became a 0x06 and 0x05 respectively.

TIME(7) result: 0x07F7AF30594B
TIME(6) result: 0x06CC44EB8807
TIME(5) result: 0x05AE53E4C000

Jemma figured it out a few seconds before I did: the leading byte (the 0x07) is the precision of the DATETIME2 or TIME data type.

Summary

The reason that a DATETIME2 (or 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.