Often it is important to know how many inserts occur per hour in a transaction table OR how many payments are made per hour OR to retrieve the hourly report, whatever term it is, the necessity is to produce a report from a datetime column based on hours. If the date column is SMALLDATETIME or DATETIME, the CONVERT function can be divided separately into years , months, days, hours, minutes and seconds.
The below is a test data along with an example.
CREATE TABLE Test1(
AccountID INT,
AccountNumber VARCHAR(20),
PaidAmount DECIMAL,
PaidDate SMALLDATETIME
)
INSERT INTO Test1 VALUES(221001, ‘A1020101’, 2000.00, ‘2010-01-20 13:10:00:000’)
INSERT INTO Test1 VALUES(221002, ‘A1020102’, 3300.00, ‘2010-01-20 13:33:00:000’)
INSERT INTO Test1 VALUES(221002, ‘A1020103’, 1100.00, ‘2010-01-20 15:01:00:000’)
INSERT INTO Test1 VALUES(221002, ‘A1020104’, 5400.00, ‘2010-01-20 15:49:00:000’)
INSERT INTO Test1 VALUES(221002, ‘A1020105’, 5200.00, ‘2010-01-20 16:30:00:000’)
INSERT INTO Test1 VALUES(221002, ‘A1020106’, 3400.00, ‘2010-01-20 16:59:00:000’)
INSERT INTO Test1 VALUES(221002, ‘A1020107’, 1200.00, ‘2010-01-20 17:10:00:000’)
INSERT INTO Test1 VALUES(221002, ‘A1020108’, 4200.00, ‘2010-01-20 18:22:00:000’)
INSERT INTO Test1 VALUES(221002, ‘A1020109’, 2100.00, ‘2010-01-20 18:43:00:000’)
INSERT INTO Test1 VALUES(221002, ‘A1020110’, 3400.00, ‘2010-01-20 19:12:00:000’)
INSERT INTO Test1 VALUES(221002, ‘A1020111’, 5500.00, ‘2010-01-20 22:19:00:000’)
–Verify the inserted data
SELECT * FROM Test1
— Generating Hourly Report
SELECT
CONVERT(VARCHAR(10), PaidDate, 101) Day,
CONVERT(VARCHAR(2), PaidDate, 108) Hour,
SUM(PaidAmount) Amount
FROM Test1
GROUP BY
CONVERT(VARCHAR(10), PaidDate, 101),
CONVERT(VARCHAR(2), PaidDate, 108)
Big Data and SQL 
One comment