Your result set is probably like:
02/04/2013 12:00:20.000 120 450 230.00 4500789
02/04/2013 13:02:20.000 120 450 230.00 4500789
02/04/2013 15:12:22.000 120 450 230.00 4500789
03/04/2013 11:05:01.000 000 300 150.00 678009
03/04/2013 13:15:18.000 000 300 150.00 678009
03/04/2013 17:23:05.000 000 300 150.00 678009
It's because the datetime data type field contains a date and time value.
All you need to do id to remove the time part, using
CONVERT() OR CAST() function[
^]
Note: The
data types[
^] collection depends on MS SQL Server version.
MS SQL 2008 and higher has a
date[
^] data type. So, all you need to do is to use something like that:
SELECT CONVERT(DATE,sp.tran_date) AS billdate ...
...
GROUP BY CONVERT(DATE,sp.tran_date)...
For MS SQL 2005 you need to use some trick:
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(30),sp.tran_date,112)) AS billdate ...
GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR(30),sp.tran_date,112))...
So... Finally it should looks like (MS SQL 2005 version using aliases):
SELECT CONVERT(DATETIME,CONVERT(VARCHAR(30),sp.tran_date,112)) AS billdate, SUM(it.iQty), AVG(it.iRate as Rate), SUM(it.iRate * it.iQty) AS amt
FROM dbo.SPBillTerm AS bt RIGHT OUTER JOIN dbo.Item AS im RIGHT OUTER JOIN dbo.SalePurchase AS sp LEFT OUTER JOIN
dbo.GL AS gl ON sp.ac_code = gl.AcCode LEFT OUTER JOIN dbo.IT AS it ON sp.Code = it.SPCode ON
im.Code = it.ItemCode ON bt.SPCode = sp.Code
WHERE CONVERT(DATETIME(CONVERT(VARCHAR(30), sp.tran_date,112)) BETWEEN @frmdate AND @todate AND sp.tran_type=@trantype
GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR(30),sp.tran_date,112))
ORDER BY sp.tran_no, it.iRate, it.iRate * it.iQty, it.iQty