ODBC date/time Extension Option in SQL Server

Words: 329

Time to read: ~ 2 minutes

At this stage of my career with Transact-SQL, I would like to think that I have the language mostly mapped. If there is something that seems new or unusual, then it often comes down to the developers marking the code to make replacing it more effortless.

In case you don’t grasp what I’m on about, the below piece of code shows it pretty well.

SELECT N'##datetime##' AS search_and_replace;

<clickbait>I had never seen this before</clickbait>

Can you imagine my shock when I came across a piece of code that not only was not for finding and replacing but even though I did not think it would compile, it did!

If you can imagine my shock, then you’re going to need to increase it more when I tell you that there are a whole family of the same functions!
Here is the code that threw me for a loop the first time I saw it.

SELECT {d '1970-01-01'};

Researching

There are more than just {d '<datetime>'} as well!

SELECT {d '2020-01-01'} AS sample_date,
       {t '12:31:56'} AS sample_time,
       {ts '2020-01-01 12:31:56'} AS sample_datetime;

There’s a whole page in Microsoft documentation about them.

It’s not just date and time functions but string function as well.

SELECT LEN(N'This string is 33 characters long') AS string_length,
       {fn BIT_LENGTH(N'This string is 33 characters long')} AS string_bit_length,
       {fn OCTET_LENGTH(N'This string is 33 characters long')} AS string_nvarchar_octet_length,
       {fn OCTET_LENGTH('This string is 33 characters long')} AS string_varchar_octet_length,
       DATALENGTH(N'This string is 33 characters long') AS string_nvarchar_datalength,
       DATALENGTH('This string is 33 characters long') AS string_varchar_datalength;

From whence camest thou?

My Google-Bing-Fu located these functions in the following link.
As well as a few answers on StackOverflow regarding querying SQL Server and A.N.Other datastore.

So now that I think I’ve grasped where they came from, will I be using these going forward?

No, thanks.

Oh, and in case you were wondering how I got the “ODBC date/time extension option” information, good error messages are irreplaceable.

SELECT {date '2020-01-01'} AS sample_date;