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.googlegroups.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:
> > 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|||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:
> 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:
> >
> > > 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|||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.googlegroups.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:
>> 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:
>> >
>> > > 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
>

Query using datetime datatype

hello,
so i have a table with column name Date with datetime as it's datatype. I'm
trying to run a select statement on it that will give me all rows where my
Date column has a datetime of 30 days or more. Can anyone help? I tried using
datediff but can't get it to work.
Thanks in advance!"FS" <FS@.discussions.microsoft.com> wrote in message
news:6A6874E0-38C5-4257-BA2A-4EEFF4D3A181@.microsoft.com...
> hello,
> so i have a table with column name Date with datetime as it's datatype.
> I'm
> trying to run a select statement on it that will give me all rows where my
> Date column has a datetime of 30 days or more. Can anyone help? I tried
> using
> datediff but can't get it to work.
> Thanks in advance!
WHERE dt <= DATEADD(DAY,-30,CURRENT_TIMESTAMP);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Query to get values from datetime column into comma separated text

Hi All

I am working on a query to get all the datetime values in a column in a table into a comma separated text.

eg.

ColumnDate
--------
2005-11-09 00:00:00.000
2005-11-13 00:00:00.000
2005-11-14 00:00:00.000
2005-11-16 00:00:00.000

I wanted to get something like

2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16

Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks

DECLARE @.List varchar(8000)
SET @.List = ''
select @.List = @.List + convert(varchar, datefield, 102) + ',' from MyTable

Just be careful, because a varchar can hold only 8000 characters, so if your results are more then they are chopped off. For more info on CONVERT function look in the BOL.