Click here to Skip to main content
15,917,628 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All

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


my Query:

SQL
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))
Posted
Comments
Thava Rajan 22-Jan-15 5:27am    
what is the purpose of this line
and (convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0), 108))
prasanna.raj 22-Jan-15 5:34am    
for convert time like '05:11:40'
Thava Rajan 22-Jan-15 5:40am    
it is in where condition do you compare with any thing?
if yes where is the other part
i no then you should put this in the select statement isn't it?
prasanna.raj 22-Jan-15 6:07am    
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')

it's also showing error

1 solution

In your WHERE clause you have
and (convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0),  108))


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

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

[Edit]For the benefit of the curious or anyone experiencing a similar issue...
The OP appears to have changed the query to
SQL
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:
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.
The problem is obviously that SUM which to be honest I hadn't spotted.
One solution is to use a HAVING clause:
SQL
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')
or you can use a CTE and use the column alias in the AND clause
SQL
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'
[2nd suggestion posted at How to use Alias in where cause in sql server[^] by @RAHUL(10217975) - bug corrected by me]
 
Share this answer
 
v2
Comments
prasanna.raj 22-Jan-15 6:13am    
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')

how to solve this...
CHill60 22-Jan-15 6:31am    
And what is the error now?
Solution updated

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900