Group By with diff data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group By with diff data

Author

Topic

dcrooks
Starting Member

4 Posts

Posted - 2006-06-06 : 12:40:09

I want to group by a datetime field by the date only.
This is not working for me:

Select detailid, convert(datetime,convert(varchar,detaildate,101)) from table1 where detaildate > '2006-05-01' and detaildate < '2006-05-05' group by detailid, convert(datetime,convert(varchar,detaildate,101))

Any ideas? Thanks!
David

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-06-06 : 14:03:18

DEFINE not working....'no data being returned'...or 'an error message being produced'???

dcrooks
Starting Member

4 Posts

Posted - 2006-06-06 : 17:34:57

Not working as the grouping is not working. I get data and no errors. It is ignoring the convert function which might be a clue. Thanks!
David

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-06-06 : 18:59:58

Is detaildate in datetime format? If so, why are you converting it to datetime again after converting it to varchar? If you want to group by the date element only:

Select detailid, convert(varchar,detaildate,101)
from table1
where detaildate > '2006-05-01'
and detaildate < '2006-05-05'
group by detailid, convert(varchar,detaildate,101)

Actually, you don't really need to group by here either, since you aren't selecting any aggregate functions. Can just do this:

Select distinct detailid, convert(varchar,detaildate,101)
from table1
where detaildate > '2006-05-01'
and detaildate < '2006-05-05'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-06 : 19:07:58

This will return your data with the date only and time set to 00:00:00.


Select
	detailid,
	dateadd(dd,datediff(dd,0,detaildate),0),
	count(*)
from
	table1 
where
	-- First date you want
	detaildate >= '2006-05-01' and
	-- First date you don't want
	detaildate < '2006-05-05' 
group by
	detailid,
	dateadd(dd,datediff(dd,0,detaildate),0)
order by
	detailid,
	dateadd(dd,datediff(dd,0,detaildate),0)

CODO ERGO SUM

dcrooks
Starting Member

4 Posts

Posted - 2006-06-07 : 09:44:52

OK, so I found the problem with the convert. Thanks!

David

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources