Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.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.

share|improve this question
up vote 18 down vote accepted

It's ODBC 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 things, like:

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, and 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:

SELECT @d - 1;


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.

share|improve this answer
This escape syntax is supported by JDBC as well. – a_horse_with_no_name Apr 4 '13 at 18:23

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.