26

Recently I've been looking through some fairly old stored procedures that were written for SQL Server 2005, and I've noticed something that I don't understand. It appears to be some type of function call.

A sample:

SELECT o.name, o.type_desc, o.create_date
FROM sys.objects o
WHERE o.create_date < {fn Now()} -1;

This will display all rows from sys.objects that have a create_date prior to 24 hours ago.

If I display the execution plan for this query, I see that {fn Now()} is replaced with getdate() by the Database Engine:

SELECT [o].[name],[o].[type_desc],[o].[create_date] 
FROM [sys].[objects] [o] 
WHERE [o].[create_date]<(getdate()-@1)

Clearly, using {fn Now()} is far more obtuse than GetDate(). I for one will avoid this syntax like the plague since it is undocumented.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323

1 Answers1

32

It's ODBC escape syntax, and the engine knows what its own implementation is, and swaps it out, as you've seen in the execution plan. There are also other things, such as:

SELECT {fn curdate()},
       {ts '2016-05-24 15:19:36'}, -- not vulnerable to SET LANGUAGE!
       {guid 'D08891B4-BC25-4C7C-BAEF-3B756055AC6E'};

See the documentation here, here, here, and most importantly here. But please don't investigate and learn about this syntax; IMHO you should use the native syntax for the most part and pretend you've never heard of this stuff.

I also strongly recommend against the getdate()-1 shorthand, too, particularly if you're going back and updating old code. Be explicit and use DATEADD, since the implicit shorthand does not work with new types. For example, try:

DECLARE @d DATE = GETDATE();
SELECT @d - 1;

Result:

Msg 206, Level 16, State 2, Line 2
Operand type clash: date is incompatible with int

While you're in there, may as well add the semi-colons, too if you really want to protect your code 10 years into the future.

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624