Dates and Times in SQL Server: more functions you should never use

Previously we looked at four built-in functions to get the current date and time in SQL Server and Azure SQL Database using Transact-SQL (T-SQL). We identified that out of the options provided, SYSUTCDATETIME() is the recommended method because it relies on Coordinated Universal Time (UTC), and uses the DATETIME2 data type which has a much higher[…]

Dates and Times in SQL Server: DATETIMEOFFSET

This post continues our look at date and time data types in SQL Server. SQL Server 2008 introduced new data types to handle dates and times in a more intelligent way than the DATETIME and SMALLDATETIME types that we looked at previously. This week, we look at the last new data type, DATETIMEOFFSET. If you’d like[…]

Dates and Times in SQL Server: DATETIME2

This post continues our look at date and time data types in SQL Server. SQL Server 2008 introduced new data types to handle dates and times in a more intelligent way than the DATETIME and SMALLDATETIME types that we looked at previously. This week, we look at the DATETIME2 data type. I’m not the first person[…]

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