To group by a datetime column without considering the time component, you can use the CAST or CONVERT Transact-SQL function to truncate the time part of the datetime values before performing the grouping. Let us see how to do it using both the methods. To start with, let us create a sample table and data.
/* Create a sample table */
CREATE TABLE MyTecBits_Sales (
SaleDate datetime,
Amount numeric(10, 2)
);
/* Insert sample data */
INSERT INTO MyTecBits_Sales (SaleDate, Amount)
VALUES
('2023-03-01 08:30:00', 100.50),
('2023-03-01 12:45:00', 75.25),
('2023-03-02 10:15:00', 50.75),
('2023-03-02 16:20:00', 120.00),
('2023-03-03 09:00:00', 60.50);
Now, let us use this table to group the data by date without considering the time component:
Using CAST
SELECT CAST(SaleDate AS date) AS DateOnly,
SUM(Amount) AS Total
FROM MyTecBits_Sales
GROUP BY CAST(SaleDate AS date);
Using CONVERT
SELECT CONVERT(date, SaleDate) AS DateOnly,
SUM(Amount) AS Total
FROM MyTecBits_Sales
GROUP BY CONVERT(date, SaleDate);
The CAST or CONVERT function is used to extract only the date part from the datetime values, effectively ignoring the time component during grouping.
Reference
- More about CAST and CONVERT at Microsoft Docs.