SQL Server Performance Forum – Threads Archive
SQL Datetime problem
I am trying to get the sum of amount’s paid for certain dates. The database stores date and time. When I group by the date it doesn’t group because the times are different. SELECT Sum(Amount) as Amount, DatePaidFROM [Transaction Manager].dbo.tmTransaction t INNER JOIN
[Transaction Manager].dbo.tmExtendedInfo ex ON t.OriginalTransID=ex.OriginalTransID
INNER JOIN vpUserStore us ON ((us.vpUserID=1) AND (t.Processor=us.Processor) AND ((us.MerchantAll=1) OR (t.Merchant=us.Merchant)))
WHERE (t.TransID=t.OriginalTransID)
AND (t.TransactionType=’D’)
AND (t.DatePaid BETWEEN ’02/07/2006′ AND ’02/08/2006 23:59:59′)
Group BY t.DatePaid How can I get it to group by the dates and disregard the time? Thanks.
Replace your references to the DatePaid column, both in the SELECT part and the GROUP BY part, with this: CONVERT(VARCHAR(10), DatePaid, 120)
Thanks Adriaan!
>>AND (t.DatePaid BETWEEN ’02/07/2006′ AND ’02/08/2006 23:59:59′) To be of safer side, you should always use universal format yyyymmdd or yyyy-mm-dd Also read this
http://www.sql-server-performance.com/fk_datetime.asp Madhivanan Failing to plan is Planning to fail
Another way, without using CONVERT would be
USE TEMPDB
CREATE TABLE #dummy
(
c1 INT
, d1 DATETIME
)
INSERT INTO #dummy VALUES(4,’20050207 12:33:00′)
INSERT INTO #dummy VALUES(4,’20050207 10:33:00′)
INSERT INTO #dummy VALUES(2,’20050208 10:34:00′)
INSERT INTO #dummy VALUES(6,’20050209 11:33:00′)
INSERT INTO #dummy VALUES(2,’20050208 11:34:00′) SELECT
DATEADD(DAY,DATEDIFF(DAY,0,d1),0) Stunden
, AVG(c1) Durchschnitt
, COUNT(*) Anzahl
FROM #dummy
GROUP BY DATEADD(DAY,DATEDIFF(DAY,0,d1),0)
ORDER BY Stunden DROP TABLE #dummy Stunden Durchschnitt Anzahl
—————————————————— ———— ———–
2005-02-07 00:00:00.000 4 2
2005-02-08 00:00:00.000 2 2
2005-02-09 00:00:00.000 6 1 (3 row(s) affected)
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
]]>