日付でgroup by(SQL Server)

AdventureWorks2012のProductModelテーブル

SELECT [ProductModelID]
      ,[Name]
      ,[ModifiedDate]
  FROM [Production].[ProductModel]

(結果)

ProductModelID Name                                               ModifiedDate
-------------- -------------------------------------------------- -----------------------
1              Classic Vest                                       2007-06-01 00:00:00.000
2              Cycling Cap                                        2005-06-01 00:00:00.000
3              Full-Finger Gloves                                 2006-06-01 00:00:00.000
4              Half-Finger Gloves                                 2006-06-01 00:00:00.000
5              HL Mountain Frame                                  2005-06-01 00:00:00.000
...
127            Rear Derailleur                                    2007-06-01 00:00:00.000
128            Rear Brakes                                        2007-06-01 00:00:00.000

(128 行処理されました)

日付でgroup by

SELECT convert(char(10), modifieddate, 111),
       count(convert(char(10), modifieddate, 111))
  FROM [Production].[ProductModel]
  group by convert(char(10), modifieddate, 111)

こんな書き方でいいのかなぁ？

(結果)

           
---------- -----------
2002/05/02 1
2005/06/01 12
2006/06/01 43
2006/11/20 8
2007/06/01 55
2009/05/16 9

(6 行処理されました)

参照したURL:
CAST および CONVERT (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms187928.aspx

(2014.3.4)

SELECT convert(char, modifieddate, 111),
       count(*)
  FROM [Production].[ProductModel]
  group by convert(char, modifieddate, 111)