Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 7 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 {fn curdate()}. Some are documented here (note that Now() isn't listed), but please don't investigate and learn about this syntax; IMHO you should use the native syntax 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.

share|improve this answer
    
Done, Done, and Done. Thanks again! –  Max Vernon Apr 4 '13 at 16:24
    
This escape syntax is supported by JDBC as well. –  a_horse_with_no_name Apr 4 '13 at 18:23

Your Answer

 
discard

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.