I think there are two questions here (SQL and url filtering), but I think the confusion has the same source. LabKey currently only supports a DateTime type, so most all stored data has a time component whether or not the default formatting shows date and time or just date.

So in the GROUP BY case, it's possible that the data is not grouping together because the time components are different. Try

  SELECT CAST(date as SQL_DATE), count(*)
  FROM ...
  GROUP BY CAST(date as SQL_DATE)

For the filtering dates in the url (~dateeq rather than ~eq) we do some trickyness so that

  X~dateeq=2011-09-13

becomes
  X BETWEEN 2011-09-13T00:00:00 and 2001-09-14T00:00:00
  (24 hours on that day)

rather than
  X = 20011-09-12T00:00:00
  (exact time match)