April 3, 2008 | SQL Server

I'll trade you consistency for meeting your deadline

While playing with the new Policy-Based Management (PBM) features of SQL Server 2008 the other day, I came across a really annoying syntax implementation that is going to trip up a lot of people unless it is fixed.  We all know how DATEADD works:


Sadly, because an expression for a condition is validated using C# and not T-SQL, the syntax needs to be slightly different:


Note the single quotes around 'DAY'… this syntax, obviously, will not work in T-SQL anytime soon:

Msg 1023, Level 15, State 1, Line 1
Invalid parameter 1 specified for dateadd.

So, imagine I am creating and testing a big WHERE clause in a query window, and once I have it right, I want to copy and paste it into the expression editor for a condition, because I want to use that WHERE clause to enforce or monitor some policy.  Now I need to save two versions of it; the original T-SQL version (should I need to modify it later), and the condition-compatible version — after meticulously adding single quotes by hand (or writing my own parser that will add them for me).  For example, would you really want to be going through large expressions like this, and "correcting" them?


And this, only if I know in advance that the DATEADD syntax needs to be different (it is not the only function affected, by the way — DATEPART() and DATEDIFF() have the same restrictions).  Note that because I forgot to put single quotes around 'DW', I will get an error message.  The error message that I receive is far less than helpful, and is the same regardless of where or how often I forgot single quotes, or even if I left out one of the parentheses:

Error parsing 'DATEADD('DAY',1-DATEPART(DW,GETDATE()),DATEDIFF('DAY',0,DATEADD('MINUTE',DATEDIFF('DAY',0,GETDATE()),GETDATE())))'. Make sure string constants are enclosed in single quotes and facet properties are prefixed with '@' sign.

It would be great to have a helpful addendum, indicating at least the first syntax error encountered, for example "Incorrect syntax near 'DW'"…

I espoused about much of this on Connect, of course:

PBM : Expressions like DATEADD require inconsistent and unintuitive syntax

If you think consistency is important, please consider voting. Unfortunately, due to time constraints around the looming RTM date, it is unlikely this will be fixed. But one can hope.

There are some other cases in the past where some decision was made on a developer's computer somewhere, and by the time the decision came into question, it was too late to correct.  Well, I have three examples that come to mind immediately, and one of them was, in fact, changed at the last minute.

This data type covers pretty much everything, doesn't it?  Why not be consistent with the INT or CHAR types (BIGDATETIME or DATETIME(MAX))?  Do you envision the need for, say, EVENBIGGERDATETIME?  Yet the name implies that they are leaving room for a higher-scale or higher-precision date/time data type (DATETIME3?).  Or maybe the name was chosen by an employee that was recruited from Oracle.

This was a very unfortunate naming blunder, since the data type is equivalent to ROWVERSION and has nothing to do with date or time at all.  But the implication given by name alone leads a lot of people to add a TIMESTAMP column to their table, only later to seek help in the forums, asking how to display their TIMESTAMP values as DATETIME, or perform a WHERE clause to filter by DATETIME.  We have asked repeatedly that this alias for ROWVERSION be marked as deprecated, and yet in sys.types in SQL Server 2008, TIMESTAMP appears, but ROWVERSION does not.  🙁

DATE and TIME as CLR types
When SQL Server 2005 was in beta, the SQL team thought it would be great to use DATE and TIME as a way to demonstrate the power of the new CLR types.  Unfortunately, the new types did not play well with the other DATETIME data types, the new SSMS GUI, or built-in functions like DATEADD and DATEDIFF.  Thankfully, a rather large group of us were so vocal in our complaints, that before ship date, they agreed to cut the feature until they could get it right.  There are still a few issues with the types as implemented in SQL Server 2008, but trust me, we are in much, much, much better shape now.

6 comments on this post

    • AaronBertrand - April 3, 2008, 5:30 PM

      As an addendum, I want to be clear that I am not against Policy-Based Management.  In fact, I think it is a fantastic feature, and once mature, it will be a very powerful tool for anyone who manages at least one complex SQL Server.  
      If you have been reading my blog here, you should already be aware that I am very enthusiastic about SQL Server 2008 in general, as I think it is a ground-breaking release.  The folks in Redmond have done some fantastic work both improving existing functionality (most notably, for me, in SSMS) and creating new functionality (not just PBM, but also the new DATE/TIME data types, filtered indexes/statistics, and some great data compression enhancements).
      I just think little things like suddenly requiring quotes around DATEADD arguments, which are going to cause confusion for so many users, can be avoided.

    • Peter W. DeBetta - April 3, 2008, 6:33 PM

      I must defend the PBM folks.
      The DateAdd, DateDiff, and DatePart functions are not T-SQL functions, but rather .NET functions that just happen to have the same name (you gotta love Reflector). If you examine the other functions available in the condition's Expression Editor, you will notice some other that are remarkably similar to those found in T-SQL, and then others that do not exist in T-SQL at all, such as ExecuteSql, BitwiseAnd, Array, and so on. And since these functions are not a subset of T-SQL, but rather, some .NET methods, I never expected these functions to be identical to their T-SQL namesakes.
      That being said…
      The argument is essentially an enumeration (which it how it "appears" to be in T-SQL), so perhaps, instead of string arguments, the datepart should be an enumerator, such as DatePart.DayOfWeek. From what I can tell, this argument has to be converted to a string at some point when it is serialized as XML (which is how it is stored behind the scenes in the "expression" column of the msdb.dbo.syspolicy_conditions table).
      Also, it wouldn't take too much to use RegEx in Find and Replace to make the appropriate changes. (Hmmm, note to self, work on RegEx for…)

    • AaronBertrand - April 3, 2008, 7:34 PM

      Hey, I am not trying to attack the PBM folks.  But regardless of the reason, my opinion is that the expression should either be consistent with T-SQL, or clearly .NET-based.  Having this almost T-SQL syntax is downright confusing.

    • Peter W. DeBetta - April 3, 2008, 8:03 PM

      I know you not attacking them – I didn't mean to imply that. Sorry about that.
      I can see your point, but it's just the one argument from two functions, DateAdd and DatePart (there is no DateDiff function available in condition expression).

    • AaronBertrand - April 3, 2008, 8:22 PM

      Hmm, I thought for sure I had tried DATEDIFF.  Apparently not, it returns the same error as GETUTCDATE and CURRENT_TIMESTAMP.  And if you try to use it in the "Field" expression instead of "Value" expression, you get an unhandled exception.  Yuck!
      Anyway, that just gives my argument more weight… since there are only two cases (that I have found so far; I haven't tried at length), doesn't that make it easy to allow the more familiar syntax, and translate it on the way, instead of expecting users to know how to translate it themselves?  Very simple regex on their part, so I don't have to do it… find DATEPART or DATEADD, and if there aren't already single quotes around the first argument, add them, then pass the expression down to wherever it is going.
      Or, require a different syntax altogether.  I like your enumeration idea, but it is probably about 6 months too late.  ðŸ™

    • Paul Nielsen - April 12, 2008, 6:46 PM

      great post
      Timestamp is one of my pet peeves. I agree, let's deprecate timestamp and stick with the ANSI standard RowVersion

Comments are closed.