grouping by date - 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

SQL Server Development (2000)

grouping by date

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-07-12 : 12:53:15

this gives me the results i want but splits up the day/month/year. how can i group by just the date (not time) and return the date as one field?

select month(dtcreated) as [Month],day(dtcreated) as [Day],year(dtcreated) as [Year],count(*) as Hits,vcmarketer as Marketer from tblhits h join tblmarketers m on m.intmarketerid=h.intmarketerid group by month(dtcreated),day(dtcreated),year(dtcreated),vcmarketer order by month(dtcreated),day(dtcreated),year(dtcreated),vcmarketer

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-12 : 13:19:20

select
dtCreated = convert(datetime,convert(nvarchar,dtcreated,101)),
count(*) as Hits,
vcmarketer as Marketer
from tblhits h
join tblmarketers m
on m.intmarketerid=h.intmarketerid
group by convert(datetime,convert(nvarchar,dtcreated,101)),vcmarketer
order by dtCreated,vcmarketer

Corey

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-07-12 : 13:22:01

can i return the dtCreated field using a different field name such as [date]?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-12 : 13:24:20

sure.

Corey

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-07-12 : 13:25:34

how? i tried changing the field name and also adding as [Date] and both returned errors

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-12 : 13:33:49

select
[date] = convert(datetime,convert(nvarchar,dtcreated,101)),
count(*) as Hits,
vcmarketer as Marketer
from tblhits h
join tblmarketers m
on m.intmarketerid=h.intmarketerid
group by convert(datetime,convert(nvarchar,dtcreated,101)),vcmarketer
order by [date],vcmarketer

Corey

aiken
Aged Yak Warrior

525 Posts

Posted - 2004-07-12 : 18:36:10

select
convert(datetime,convert(nvarchar,dtcreated,101)) as [date],
count(*) as Hits,
vcmarketer as Marketer
from tblhits h
join tblmarketers m
on m.intmarketerid=h.intmarketerid
group by convert(datetime,convert(nvarchar,dtcreated,101)),vcmarketer
order by convert(datetime,convert(nvarchar,dtcreated,101)),vcmarketer

Cheers
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources