THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

The "by design" abuse

In the last months, I got the infamous "it's by design" answer for many bugs/issues/irrational behaviors I posted to Microsoft. For most of them I used the Connect web site, in a couple of cases I opened a formal incident to Microsoft support.

There are a lot of story (in and outside Microsoft) about the "it's by design" excuse and most of them are myths and urban legends. Unfortunately, today I received a notification about a bug closure with the "by design" comment that has the consequence to invalidate Microsoft documentation (ok, a small part of...).

The bug is related to SQL Server. One customer wrote me about a strange issue of the ODBC international date constant syntax {d 'yyyy-mm-dd'}. I never used that, since I prefer the yyyymmdd syntax, but Microsoft effectively suggests using this in the "Writing International Transact-SQL Statements" of SQL Server 2005 Books Online.

As a consultant, I was a little bit shamed because I never used it and I wasn't aware of the issues raised by changing the language settings (you know, this syntax should be used just because it's language neutral...). I double checked the whole thing (I always assume it's my fault before suspecting it's a product bug...) and then I posted this bug on the Connect web site. For the lazy of you, this is the repro code.

1:  USE tempdb
2:  GO
IF NOT EXISTS (SELECT * FROM sysobjects where name = 'ProblemDate') BEGIN CREATE TABLE [dbo].[ProblemDate](
[DateA] [datetime] NOT NULL ,
[DateB] [datetime] NOT NULL
6:  )
IF NOT EXISTS (SELECT * FROM ProblemDate) BEGIN INSERT INTO [ProblemDate] ([DateA],[DateB])
VALUES ({ d '2004-09-15'} ,{ d '2004-03-30'}) END
11:  GO
SET language english
13:  go
-- The select run correctly
SELECT CASE WHEN ProblemDate.DateA > {d '2006-11-21'} THEN 'y' ELSE 'x' END
16:  FROM
WHERE ProblemDate.DateB={d '2004-03-30'}
18:  GO
SET language italian
20:  GO

-- The same select, got an error
SELECT CASE WHEN ProblemDate.DateA > {d '2006-11-21'} THEN 'y' ELSE 'x' END
23:  FROM
WHERE ProblemDate.DateB={d '2004-03-30'}
25:  GO

If you were able to say that line 22 will generate a date conversion error before running the code, I'd be ready to offer you a pizza everywhere you are in the world.

Now, it seems that I need to prepare a delivery of pizzas to Redmond, since the bug has been closed as "By Design". Unfortunately, there are no comments to explain such a decision.

I can make several hypotheses:

  • The bug has been fixed in a future release and someone pressed the wrong button closing the bug. That's the optimistic one.
  • The bug has been considered not a bug because I need to pay some pizzas to someone.
    • In that case, I will claim the pizzas from the documentation team, which has misguided those few people that RTFM :-)
  • The bug has been closed by someone that read one comment to the bug that pointed out a similar issue on the 'yyyy-mm-dd' syntax - but it is not a good explanation of the behavior that differs from documentation of the product.

I don't know. However, the issue I described is not the real problem; it's just an example to illustrate what happens on the Connect web site. In this case, the answer "by design" has been posted 3 month after the bug submission. Sometime I haven't seen an answer for many more months. However, it really depends on the team, there are other guys who typically answer in a few days (or some week in the worse case).

I really don't like writing this kind of posts, but I know that many people in Microsoft monitor blogs like mine and this is a message in a bottle for them. Guys, the whole Connect site isn't very useful if you will continue to answer with "by design" / "we don't have resource now" / "it's not on our plan for the next release (!)". The last one is my nightmare, because it means that this necessary feature/bug/whatever will be implemented/corrected in a production code that won't be released until 2011 (assuming that the 3 years gap between SQL Server releases will be maintained).

Posting on the Connect site has a time cost. To me (writing) and to Microsoft (reading and -sometime- answering). I typically post on Connect only after a search in newsgroups and web sites, only when I'm able to describe a repro of the issue. Until today, I submitted 44 comments (bugs and suggestions) for SQL Server 2005 - I'm not the first, but considering an average of 30 minute per post (and I'm not calculating the time to discover the bug of to recognize the need for a feature/change) it means 22 hours of work. Most of my posts are still active. Many don't have an answer, but I know that Katmai should have improvements covering some of my suggestions. A better update of these submissions would be appreciated...

I think that the Connect web site is a great resource, but it's not comparable to the kind of support that is offered by some ISV (I use other third-party tools related to SQL Server and .NET programming and they are years-light more responsive). I know, the small size of a company might help in being very efficient in customer support, but I can't believe that a company like Microsoft is not able to improve the Connect efficiency. In the last 7 days there were 48 new bugs and 31 new suggestions for the whole SQL Server platform (which includes SSIS, SSAS, SSRS, SSNS, SSBS). Are these numbers big? I don't know. I would say "no, they are not so big for a company like Microsoft".

Finally... I know, probably there are other priorities. But, in this case, please don't abuse the "by design" way to fix your bugs.

UPDATE - This bug has been fixed in SQL Server 2008!

Published Thursday, July 19, 2007 11:34 PM by Marco Russo (SQLBI)



Davide Mauri said:

Hi Marco

though I really agree with you that Connect could be a better site and that closing a incident just saying "it's by default" is really not a nice thing, I fear that in this case the "bug" you report isn't a bug at all.

In BOL is state quite clearly (not so clearly, anyway) that the only format that is not dependent from international settings is the ISO8601 format:

"this format is not affected by the SET DATEFORMAT or SET LANGUAGE settings."

Even in the BOL link you posted ("Writing International Transact-SQL Statements

") is documented that "Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers, should use the unseparated numeric strings. For example, yyyymmdd as 19980924."

And thus the behaviour your customer encountered is expected.

In addition in this case the fact that the formato yyyy-mm-dd is not handled as the format yyyymmdd as it should be, since from the ISO 8601 point of view they should be identical, is "by design", again documented here

I'm not trying to defend Connect or MS, but this time I don't think they faulted. Of course a better explantion (as the one I just gave) would surely by better that a simple "it's by design".


July 20, 2007 11:42 AM

Marco Russo (SQLBI) said:

Hi Davide (manowar),

as you know, I disagree from your point of view.

I can understand that the {d ...} syntax is broken in SQL Server. The point is that here ( Microsoft includes, in a page titled "Writing International Transact-SQL Statements", says that "ADO, OLE DB, and ODBC applications should use the ODBC timestamp, date, and time escape clauses [...]". In a following point, they are talking about applications that are using "other APIs".

Now, if my customer is using a SSIS package with a OLE DB connection to SQL Server, I suppose that reading this page he could use the {d...} syntax (he understood that this syntax is the safer one!).

If we want to move this bug in a documentation bug... it's ok, but I still can't understand why in the same SELECT statement there are two constants that are intepreted in two different ways, with two different results under the same LANGUAGE setting!

Anyway, the whole point of my post is not to claim on this bug, but to make evidence of the not-so-good use of Connect site that Microsoft is doing, in my opinion.

Finally: if I would try using the "by design" excuse with my customers, this would be the result...

Customer: "Marco, this is a bug, fix it!"

Marco: "It's not a bug, it's by design"

Customer: "What? So, your design is broken, fix it!"

Marco: "..."

I'm not a customer, I'm a technician, I can understand technical issues... but I'd like a better Microsoft's approach to this kind of issues (I described only one example) without the abuse of the "by design" answer.

July 20, 2007 12:23 PM

AUggetti said:

But if you use this statement:

SELECT CASE WHEN ProblemDate.DateA > CONVERT(DATETIME, { d'2006-11-21' }) THEN 'y' ELSE 'x' END

FROM ProblemDate

WHERE ProblemDate.DateB={d '2004-03-30'}


always works fine.

I think that problem is related just to


statement that doens't works fine with {d '2006-11-21'}

and not regarding how specify the date.

Try this:



SELECT { d'2006-11-21' }

FROM ProblemDate

WHERE ProblemDate.DateB={d '2004-03-30'}


So I'm not totally agree that this bug is closed "by design" too.


July 20, 2007 1:26 PM

AUggetti said:

Added: it looks like a problem with the "Implicit" conversion done into CASE statement

July 20, 2007 1:47 PM

michaelfengwang said:

Hi, Marco

After looking into more details, i think there is a misunderstanding regarding the closure of the defect as 'by design'.

The 'by design' indeed refers to the request on making the 'yyyy-mm-dd' format to be language setting non-deterministic as same as for 'yyyymmdd' and/or {d'yyyy-mm-dd'}. From that perspective, it tried to tell the truth in particular from the backward compatibility concern for the existing applications. As a side note, just want to let you and others be aware, the new date/time types (i.e. DATE, DATETIME2 and ..) being introduced in the upcoming new SQL Server release WILL have it adjusted (not for existing datetime/smalldatetime).

As i tested for the failed statement, the issue seems mainly on the value in the WHERE clause (WHERE ProblemDate.DateB={d '2004-03-30'}) rather than something purely to do with the CASE statement itself. In another word, it's a very strange one and being data related (or combined with the CASE statement).

'2004-03-30' seems a magic number here as i tried by giving other values (i.e. 2004-03-28, 2004-03-20, 2004-03-31 and ..) which all seem working fine.

BTW, sorry for the confusion. I'll make sure the bug being re-opened and looked at by corresponding developer.


July 20, 2007 6:45 PM

Marco Russo (SQLBI) said:

Thank you Michael.

I see that the message in a bottle reached the destination :-)

Have a nice week-end.


July 20, 2007 7:53 PM

Davide Mauri said:

Hi Everyone!

I used SHOWPLAN_XML to have detailed information on what's happening behind the scenes.

The date conversion in the CASE clause is done with a CONVERT_IMPLICIT operator:

<ScalarOperator ScalarString="CASE WHEN [tempdb].[dbo].[ProblemDate].[DateA]&gt;CONVERT_IMPLICIT(datetime,'2006-11-21 00:00:00.000',0) THEN 'y' ELSE 'x' END">

which behave just like the normal CONVERT operator, and in fact if we write

CONVERT(datetime,'2006-11-21 00:00:00.000',0)

we obtain the same error.

So, from my point of view the datetime conversion is working as expected.

Anyway I found a strange behaviour and, as Andrea suggested, I suspect there is something tricky with the CASE operator. This query, in fact, works perfectly:

SELECT {d '2006-11-21'}

FROM ProblemDate

WHERE ProblemDate.DateB={d '2004-03-30'}


and, strange enough, EVEN THIS WORKS:

SELECT CASE WHEN ProblemDate.DateA > {d '2006-11-21'} THEN 'y' ELSE 'x' END

FROM ProblemDate

WHERE ProblemDate.DateB={d '2004-11-21'}

so, seems that at the end a bug exists, but in the handling of CASE statment!

July 21, 2007 5:08 AM

SQLBI - Marco Russo said:

Almost two years ago I wrote a post about the abuse of the "it's by design" answer Microsoft gives to

March 16, 2009 6:46 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement