Group by date in SQL Server

Question

I am having a problem with grouping in SQL Server.

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

Here is 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 to group 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 get the difference between the beginning of the day and the time in the date, and then the deletion.

Answer 2

You can try:

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

Answer 3

Since you want to display only one result, why not use DISTINCT ?

 SELECT DISTINCT ProductCode, ... FROM ... GROUP BY ProductCode 

Answer 4

Simply put, your GROUP BY should appear somewhere in your selection. You are already converting TransactionDate to the string "date only", although DATEADD and DATEDIFF together are actually more efficient.

Therefore, just GROUP BY in the same format:

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

Answer 5

Try using CHAR (10) instead of varchar when applying your conversion. You should never declare char / varchar without length. In this case, you do not truncate enough characters, because it is varchar (30), while you really only care about the first 10 characters. In addition, your group should include an identical converter.