错误：在预期条件的上下文中指定的非布尔类型的表达式，在'）'附近。 [英] Error: An expression of non-boolean type specified in a context where a condition is expected, near ')'.

问题描述

亲爱的所有



这里我在sql查询中面对这个问题如何解决这个问题





我的查询：

Dear All

Here i've face this prob in sql query how to solve this this


my Query:

SELECT convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0),  108) as Short from Daily where
EmpCodeC='003' and ClockDateD between ( '01/Jan/2015 12:00:00 AM') and ('31/Jan/2015 12:00:00 AM')
and (convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0),  108))

推荐答案

在你的 WHERE 子句中你有

and (convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0),  108))

这不是一个条件 - 你没有将它与任何东西进行比较。您需要执行以下操作之一：



1.通过将转换后的日期与某些内容进行比较来完成该条款。使用BETWEEN或<或者>



或



2.完全删除WHERE子句的那一部分



---------------------------



为了好奇或任何遇到类似问题的人的利益......

OP似乎已将查询更改为

This is not a condition - you are not comparing it to anything. You need to do one of the following:

1. Finish off the clause by comparing the converted date to something e.g. using BETWEEN or < or >

OR

2. Remove that part of the WHERE clause altogether

---------------------------

For the benefit of the curious or anyone experiencing a similar issue...
The OP appears to have changed the query to

SELECT convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0),  108) as Short from Daily where
EmpCodeC='003' and ClockDateD between ( '01/Jan/2015 12:00:00 AM') and ('31/Jan/2015 12:00:00 AM')
AND (convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0),  108) <='02:00:00')

哪会导致错误

Which will result in the error

Quote：

聚合可能不会出现在WHERE子句中，除非它在包含在HAVING子句或选择列表中的子查询中，被聚合的列是外部引用。

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

问题显然是那个 SUM 老实说，我没有发现。

一种解决方案是使用HAVING子句：

The problem is obviously that SUM which to be honest I hadn't spotted.
One solution is to use a HAVING clause:

SELECT convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0),  108) as Short from Daily where
EmpCodeC='003' and ClockDateD between ( '01/Jan/2015 12:00:00 AM') and ('31/Jan/2015 12:00:00 AM')
HAVING (convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0),  108) <='02:00:00')

或者您可以使用CTE并使用<$中的列别名c $ c> AND 子句

or you can use a CTE and use the column alias in the AND clause

Select * from
(
SELECT convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0),  108) as Short from Daily where
EmpCodeC='003' and ClockDateD between ( '01/Jan/2015 12:00:00 AM') and ('31/Jan/2015 12:00:00 AM')
) temp
Where Short <='02:00:00'

[第二条建议发布于如何在sql server中的原因中使用别名 [ ^ ] @RAHUL（10217975 ） - 我纠正的错误]

[2nd suggestion posted at How to use Alias in where cause in sql server[^] by @RAHUL(10217975) - bug corrected by me]

这篇关于错误：在预期条件的上下文中指定的非布尔类型的表达式，在'）'附近。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！