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[…]

Post image

Be Wary of Date Formatting in T-SQL

Today’s public service announcement is a reminder to be wary of date formatting in SQL Server. On a recent mailing list discussion, one person demonstrated the following code (which I’ve adapted for this post): SET LANGUAGE ‘us_english’; GO DECLARE @StartDate DATETIME = ‘2017-07-12’; SELECT @StartDate; GO DECLARE @StartDate DATE = ‘2017-07-12’; SELECT @StartDate; GO DECLARE[…]