Group dates by month

[Willa]

I have a table that i want to query and group the records by the
month, what SQL stanment would i use?
Somthing like this? apart from i know that the "Group BY (MM/YYYY)"
wont work !:)
Select Date,Name
FROM Table
Group by (MM/YYYY)

Does this make sense?

Willa

[Erland Sommarskog]

Willa (wa*******@ncl.com) writes:

I have a table that i want to query and group the records by the
month, what SQL stanment would i use?
Somthing like this? apart from i know that the "Group BY (MM/YYYY)"
wont work !:)

SELECT convert(char(6), date_col, 112), count(*)
FROM tbl
GROUP BY convert(char(6), date_col, 112)

Or if you don't want to repeat the expression:

SELECT month, COUNT(*)
FROM (SELECT month = convert(char(6), date_col, 112
FROM tbl) as a
GRUOP BY month

If you prefer some other format of the month, you can some combination
of the convert() and substring() functions. See further in Books Online.
convert() is under the topic "CAST and CONVERT".

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp