Are there any performance downsides to using ODBC date string-literals in SQL Server? Is it better than a regular string date literal?

Question

I have a TSQL view that processes multiple gigabytes of data in a SQL Server 2016 environment. In this view, there are multiple times where I am comparing if a DateTime value is before/after a static date, traditionally represented as a string literal like '2018-07-11'.

An example comparison would be:

SELECT MyId, MyValue FROM MyTable WHERE MyDate = '2018-07-11'

While looking for a way to use a DateTime literal instead of a string, I came across examples using ODBC DateTime strings like so:

SELECT MyId, MyValue FROM MyTable WHERE MyDate = {d '2018-07-11'}

When I compare the query plan I get the same result, even when I make up more advanced queries.

I started using this format in an attempt to prevent the auto-conversion of string to DateTime in queries, but I haven't been able to find any good documentation explaining any side effects of using ODBC functions. I'm not sure if this acts the same way as a string literal or if it is interpreted as a date.

If this was a UDF or Stored Procedure, I'd have the ability to declare a DateTime variable for use in the query, but in a VIEW this is not possible, nor would it be feasible because there are a lot of DateTime literals in the actual version of the query.

So in conclusion, does someone have any concrete reasons for or against using this {d '2018-07-11'} format (besides it potentially not being valid in a non SQL Server environment)?

I want to ensure that I'm not shooting myself in the foot here on a code review.

PS: I apologize for the vague examples and semi-open-ended question, I am not allowed to disclose any actual source code.

Thanks!

EDIT: I forgot to mention that I could also use DATEFROMPARTS(2018, 07, 11), but I wasn't sure if this would be looked at weirdly by the query optimizer.

Answer 1

The ODBC literal has the slight advantage that it can never be interpreted as YYYY-DD-MM, which is possible with one internationalization setting.

You can avoid ambiguity by using 'YYYYMMDD' format. This format is not affected by settings.

I prefer not using the ODBC, just because it seems to involve more clutter in the query. I admit to also preferring the hyphenated form (consistent with the ISO standard and other databases). But you have three alternatives. Possibly the safest for general purpose, SQL-Server-only code is the unhyphenated form.