Disclaimer: It may be outdated, according to low server version, but still happens.

What is about?

Some time ago, in our application has shown some convert, quite strange, error. I will start from the beginning. At first, for sake of example, create table:

 IF OBJECT_ID('Table1') IS NOT NULL 
 DROP TABLE [Table1] 
 GO 

 IF OBJECT_ID('Table1') IS NULL 
 CREATE TABLE [Table1] 
  ( 
   [Table1Id] int NOT NULL IDENTITY(1, 1),
   [dateFrom] datetime,
   [dateTo] datetime,
   [dateException] datetime
  )
 GO 

then fill it with some data:

INSERT INTO [Table1] (dateFrom,dateTo,dateException) VALUES ('20080714','20101231','20100330')
INSERT INTO [Table1] (dateFrom,dateTo,dateException) VALUES ('20110101','20111231','20110630')
INSERT INTO [Table1] (dateFrom,dateTo,dateException) VALUES ('20120101','20121231','20120630')
INSERT INTO [Table1] (dateFrom,dateTo,dateException) VALUES ('20130101','20131231','20130630')
INSERT INTO [Table1] (dateFrom,dateTo,dateException) VALUES ('20140101','20141231','20140630')

It seems that it is relevant:

SET LANGUAGE 'polish' 

SET LANGUAGE 'german' 

As you can see, it's just an extracted, and simplified version of some production table.

Lets play!

As first we try that queries:

SELECT *
FROM [Table1] 
WHERE {ts '2010-06-30 00:00:00.000'} between dateFrom and dateTo

SELECT CASE WHEN {ts '2010-06-30 00:00:00.000'} > dateException THEN 1 ELSE 0 END [NextPeriod]
FROM [Table1] 

that works without any problems.

But now merge them into one query...

SELECT CASE WHEN {ts '2010-06-30 00:00:00.000'} > dateException THEN 1 ELSE 0 END [NextPeriod]
FROM [Table1] 
WHERE {ts '2010-06-30 00:00:00.000'} between dateFrom and dateTo 

BUMP!

For me, it's quite strange. When you run it at higher version of sql, or change language to english, or when just change

{ts '2010-06-30 00:00:00.000'}

'20100630'

Unfortunatelly, in my job, that writing of date (as ODBC API) come from client application, we use only polish or german language versions, and - and it hurts the most - we still work at server 2005 (to dig to bottom of hell, for some purpose, at compatibility level of 80...).

I was trying to work it out, but it seems that compiler is making implicit conversion of date in WHERE clause, but avoid it (somehow) at CASE in WHERE clause. So I must force to cast of date (of rather proper wrote ODBC timestamp standard!) to DATETIME.

SELECT CASE WHEN CAST({ts '2010-06-30 00:00:00.000'} AS DATETIME) > dateException THEN 1 ELSE 0 END [NextPeriod]
FROM [Table1] 
WHERE {ts '2010-06-30 00:00:00.000'} between dateFrom and dateTo  

SELECT CASE WHEN '20100630' > dateException THEN 1 ELSE 0 END [NextPeriod]
FROM [Table1] 
WHERE '20100630' between dateFrom and dateTo  

Thank you for your attention.
-- Adam Tokarski --