query works in 2005 but not in 2000

Hi,
I have the following query, it works fine in sql server 2005, but in
sql server 2000 it errors out:
DECLARE @.datestart datetime
DECLARE @.dateend datetime
set @.datestart = '2004-11-29'
set @.dateend = '2006-11-29'
select convert(varchar, (convert(datetime, convert(varchar,
test.date_created,104), 104)), 1) dt, count(apples) fruits
from testtable1 test, testtable2 od
where test.code=od.code2
and (test.method<>3)
and test.date_created between '00:00 '+@.datestart AND '23:59:59
'+@.dateend
group by convert(datetime, convert(varchar, test.date_created, 104),
104)
order by convert(datetime, convert(varchar, test.date_created, 104),
104) desc
in sql 2000 i get the following error:
Msg 8120, Level 16, State 1, Line 7
Column 'test.DATE_CREATED' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
In 2005 it returns the data fine.
I need to have it return sorted by date and grouped by date, but the
returned data needs to not display the time, thats why I was doing that
conversion in the select. Any reason why it would work in one and not
the other. Or anyway around this?
Thanks.
Put the right stuff in a subquery and make the final conversion in the
outside query. Using subqueries is usually the best way of dealing with
Group By problems.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"phil2phil" <philtwophil@.yahoo.com> wrote in message
news:1164831064.173961.83960@.16g2000cwy.googlegrou ps.com...
> Hi,
> I have the following query, it works fine in sql server 2005, but in
> sql server 2000 it errors out:
>
> DECLARE @.datestart datetime
> DECLARE @.dateend datetime
> set @.datestart = '2004-11-29'
> set @.dateend = '2006-11-29'
> select convert(varchar, (convert(datetime, convert(varchar,
> test.date_created,104), 104)), 1) dt, count(apples) fruits
> from testtable1 test, testtable2 od
> where test.code=od.code2
> and (test.method<>3)
> and test.date_created between '00:00 '+@.datestart AND '23:59:59
> '+@.dateend
> group by convert(datetime, convert(varchar, test.date_created, 104),
> 104)
> order by convert(datetime, convert(varchar, test.date_created, 104),
> 104) desc
> in sql 2000 i get the following error:
> Msg 8120, Level 16, State 1, Line 7
> Column 'test.DATE_CREATED' is invalid in the select list because it is
> not contained in either an aggregate function or the GROUP BY clause.
> In 2005 it returns the data fine.
> I need to have it return sorted by date and grouped by date, but the
> returned data needs to not display the time, thats why I was doing that
> conversion in the select. Any reason why it would work in one and not
> the other. Or anyway around this?
> Thanks.
>
|||phil2phil wrote on 29 Nov 2006 12:11:04 -0800:

> Hi,
> I have the following query, it works fine in sql server 2005, but in
> sql server 2000 it errors out:
> DECLARE @.datestart datetime
> DECLARE @.dateend datetime
> set @.datestart = '2004-11-29'
> set @.dateend = '2006-11-29'
> select convert(varchar, (convert(datetime, convert(varchar,
> test.date_created,104), 104)), 1) dt, count(apples) fruits
> from testtable1 test, testtable2 od
> where test.code=od.code2
> and (test.method<>3)
> and test.date_created between '00:00 '+@.datestart AND '23:59:59
> '+@.dateend
> group by convert(datetime, convert(varchar, test.date_created, 104),
> 104)
> order by convert(datetime, convert(varchar, test.date_created, 104),
> 104) desc
> in sql 2000 i get the following error:
> Msg 8120, Level 16, State 1, Line 7
> Column 'test.DATE_CREATED' is invalid in the select list because it is
> not contained in either an aggregate function or the GROUP BY clause.
> In 2005 it returns the data fine.
> I need to have it return sorted by date and grouped by date, but the
> returned data needs to not display the time, thats why I was doing that
> conversion in the select. Any reason why it would work in one and not
> the other. Or anyway around this?
> Thanks.
The error occurs because the SQL Server 2000 compiler sees a difference
between the SELECT list and the GROUP BY, due to the extra CONVERT you have
in the SELECT. The GROUP BY must match the SELECT with the summaried columns
removed. If you use this it should work in 2000:
DECLARE @.datestart datetime
DECLARE @.dateend datetime
set @.datestart = '2004-11-29'
set @.dateend = '2006-11-29'
select convert(varchar, (convert(datetime, convert(varchar,
test.date_created,104), 104)), 1) dt, count(apples) fruits
from testtable1 test, testtable2 od
where test.code=od.code2
and (test.method<>3)
and test.date_created between '00:00 '+@.datestart AND '23:59:59
'+@.dateend
group by convert(varchar, (convert(datetime, convert(varchar,
test.date_created,104), 104)), 1)
order by convert(datetime, convert(varchar, test.date_created, 104),
104) desc
Dan
|||Hi,
I see that the SELECT and GROUPBY need to match, but the problem is I
need to return the datetime without the date, thats why i was
converting to varchar. And I can't do the Order by as varchar as then
it's not ordering it anymore by datetime, which it needs to. If I try
to make the SELECT and GROUPBY the same, it complains about the ORDERBY
also needing to be the same, thats the part where i'm stuck, I need no
time in the return but it needs to be ordered by datetime.
Sylvain is there anyway you can give me a sample of how to put it in a
subquery and then make the final conversion outside?
Thanks.
Daniel Crichton wrote:
> phil2phil wrote on 29 Nov 2006 12:11:04 -0800:
>
> The error occurs because the SQL Server 2000 compiler sees a difference
> between the SELECT list and the GROUP BY, due to the extra CONVERT you have
> in the SELECT. The GROUP BY must match the SELECT with the summaried columns
> removed. If you use this it should work in 2000:
> DECLARE @.datestart datetime
> DECLARE @.dateend datetime
> set @.datestart = '2004-11-29'
> set @.dateend = '2006-11-29'
> select convert(varchar, (convert(datetime, convert(varchar,
> test.date_created,104), 104)), 1) dt, count(apples) fruits
> from testtable1 test, testtable2 od
> where test.code=od.code2
> and (test.method<>3)
> and test.date_created between '00:00 '+@.datestart AND '23:59:59
> '+@.dateend
> group by convert(varchar, (convert(datetime, convert(varchar,
> test.date_created,104), 104)), 1)
> order by convert(datetime, convert(varchar, test.date_created, 104),
> 104) desc
>
> Dan
|||Actually I got the subquery select working, used a select into
statement, to put it into a temp table, and then had the temp table
data return the first column as varchar, removing the time.
Thank you for the help.
phil2phil wrote:[vbcol=seagreen]
> Hi,
> I see that the SELECT and GROUPBY need to match, but the problem is I
> need to return the datetime without the date, thats why i was
> converting to varchar. And I can't do the Order by as varchar as then
> it's not ordering it anymore by datetime, which it needs to. If I try
> to make the SELECT and GROUPBY the same, it complains about the ORDERBY
> also needing to be the same, thats the part where i'm stuck, I need no
> time in the return but it needs to be ordered by datetime.
> Sylvain is there anyway you can give me a sample of how to put it in a
> subquery and then make the final conversion outside?
> Thanks.
>
> Daniel Crichton wrote:
|||phil2phil wrote:
> Hi,
> I see that the SELECT and GROUPBY need to match, but the problem is I
> need to return the datetime without the date, thats why i was
> converting to varchar. And I can't do the Order by as varchar as then
> it's not ordering it anymore by datetime, which it needs to. If I try
> to make the SELECT and GROUPBY the same, it complains about the ORDERBY
> also needing to be the same, thats the part where i'm stuck, I need no
> time in the return but it needs to be ordered by datetime.
> Sylvain is there anyway you can give me a sample of how to put it in a
> subquery and then make the final conversion outside?
>
Instead of converting, use this to "strip off" the time portion of a
date/time:
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Easy: to create a subquery, you put the query between parenthesis and you
give it an name (or alias). In your case, this should look like:
Select convert(varchar, SQ.dt, 1) as dt, fruits
From (
select convert(datetime, convert(varchar, test.date_created,104), 104) dt,
count(apples) fruits
from testtable1 test, testtable2 od
where test.code=od.code2 and (test.method<>3)
and test.date_created between '00:00 '+@.datestart AND '23:59:59
'+@.dateend
group by convert(datetime, convert(varchar, test.date_created, 104), 104)
) as SQ
order by SQ.dt desc
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"phil2phil" <philtwophil@.yahoo.com> wrote in message
news:1164899356.494835.194380@.j72g2000cwa.googlegr oups.com...
> Actually I got the subquery select working, used a select into
> statement, to put it into a temp table, and then had the temp table
> data return the first column as varchar, removing the time.
> Thank you for the help.
> phil2phil wrote:
>