Group by date in SQL Server

Question

I'm having a problem with grouping in SQL Server.

I have a datetime column but I want to group only by date,

Here's my code

SELECT ProductCode,
       ProductName,
       ProductType,
       UnitPrice,QTY,
       Amount,
       (convert(varchar, TransactionDate, 101) )AS sTransactionDate
FROM DailyTransactions 
GROUP BY TransactionDate, ProductCode,
          ProductName,ProductType,UnitPrice,QTY,Amount

RESULT:

2/17/2012 appears three times because it has different times ...

Answer 1

This should be a quick way of grouping your results by date:

SELECT  
   ProductCode,ProductName,ProductType,UnitPrice,QTY,Amount,(convert(varchar, TransactionDate, 101) )AS sTransactionDate
   FROM DailyTransactions 
   GROUP BY DATEADD(day, DATEDIFF(day, 0, TransactionDate), 0), ProductCode,ProductName,ProductType,UnitPrice,QTY,Amount

In this case we are getting the difference between the start of the day and the time in the date and then removing it.

Answer 2

You could try:

GROUP BY
  DATEPART(year, TransactionDate),
  DATEPART(month, TransactionDate),
  DATEPART(day, TransactionDate)