SQL用DataDiff查询的怪现象而引发的思考。。

今天又看到新加坡的同事发过来的一段SQL语句，还是老问题，时间对比直接用大于小于号。叹了声气后，手动给改成datediff了，可是一运行出错，错误提示如下：

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

 

为了说明方便，这里就简化一个例子好了。

 

create table a

(

   id int not null,

   val   varchar(20)

)

 

insert into a values (1, '2009-05-02')

insert into a values (2, 'abc')

insert into a values (3, '2009-12-10')

 

我的语句是这样的:

 

select * from a where id in (1,3) and datediff(day, convert(datetime,val),getdate()) < 0

 

按常理，我先找到id= 1或3的记录，然后再跟今天比较，应该会返回id=1的记录啊。可是居然出现如下错误：

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

 

可是当我执行：

 

select datediff(day, convert(datetime,val),getdate()) from a where id in (1,3)

这时候又是能够执行的。 这就是说在进行1或3的判断前，已经对id=2这条记录执行了convert，所以出错了。

 

 

由此可见，语句并不是我们想象的由上到下执行的，难道是由下向上执行的？换成如下语句:

select * from a where datediff(day, val,getdate()) < 0 and id in (1,3)

 

错误依旧。

 

晕了，不是由上到下，也不是由下到上，难道这个顺序不是固定的？ 仔细分析一下，上面一个可以执行的语句可以发现,

由于convert函数是在所查询出来的结果集上再进行的转换，而且id = 1或3的记录中的val列都是可以转换为时间类型的，所以

没有任何错误。 其他两个运行有错误的语句中，在执行的时候都是在表中所有记录的基础上，先调用convert这个条件的，所以

当遇到id=2这条记录时，就会出错。既然SQL server不是按照我们想要的顺序去执行，那么我们能不能强制让它去按我们的所要求的

顺序去执行呢？

于是乎，好用的case when 就派上用场了。我把上面的语句改为如下：

select * from a where (case when id in (1,3) then datediff(day, convert(datetime,val),getdate()) else 1 end) < 0

 

如上面所示，在where 条件中，首先判断 当 id in (1,3) ，则进行datediff和convert函数的掉用，此时只有id = 1或3的记录才会调用convert函数，所以此时肯定不会出错，其他的直接返回一个可以忽略改条件的值即可。

运行后，没有错误，并且只返回ID=1的记录。