CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
The Muffin Man
The Muffin Man

Reputation: 20004

How can I group by date time column without taking time into consideration

I have a bunch of product orders and I'm trying to group by the date and sum the quantity for that date. How can I group by the month/day/year without taking the time part into consideration?

3/8/2010 7:42:00 should be grouped with 3/8/2010 4:15:00

Upvotes: 311

Views: 465313

Answers (8)

Martin Smith
Martin Smith

Reputation: 452957

From SQL Server 2022 you can use DATETRUNC.

If your datetime column is indexed this can make better use of the order provided by it than earlier alternatives.

DECLARE @Demo TABLE
(
myDateTime DATETIME INDEX ix_myDateTime
)

SELECT DATETRUNC(DAY, myDateTime) AS Date,
       COUNT(*)
FROM @Demo
GROUP BY DATETRUNC(DAY, myDateTime)


SELECT CAST(myDateTime AS DATE) AS Date,
       COUNT(*)
FROM @Demo
GROUP BY CAST(myDateTime AS DATE)

enter image description here

Upvotes: 0

Aravindh Gopi
Aravindh Gopi

Reputation: 2166

GROUP BY DATE(date_time_column)

Upvotes: 5

Ruchira Nawarathna
Ruchira Nawarathna

Reputation: 1477

CAST datetime field to date

select  CAST(datetime_field as DATE), count(*) as count from table group by CAST(datetime_field as DATE);

Upvotes: 21

Radhakrishnan
Radhakrishnan

Reputation: 1040

Here is the example works fine in oracle

select to_char(columnname, 'DD/MON/yyyy'), count(*) from table_name group by to_char(createddate, 'DD/MON/yyyy');

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300489

GROUP BY DATEADD(day, DATEDIFF(day, 0, MyDateTimeColumn), 0)

Or in SQL Server 2008 onwards you could simply cast to Date as @Oded suggested:

GROUP BY CAST(orderDate AS DATE)

Upvotes: 32

user3169774
user3169774

Reputation: 21

Here's an example that I used when I needed to count the number of records for a particular date without the time portion:

select count(convert(CHAR(10), dtcreatedate, 103) ),convert(char(10), dtcreatedate, 103)
FROM dbo.tbltobecounted
GROUP BY CONVERT(CHAR(10),dtcreatedate,103)
ORDER BY CONVERT(CHAR(10),dtcreatedate,103)

Upvotes: 2

Kamyar
Kamyar

Reputation: 18797

In pre Sql 2008 By taking out the date part: 

GROUP BY CONVERT(CHAR(8),DateTimeColumn,10)

Upvotes: 17

Oded
Oded

Reputation: 498904

Cast/Convert the values to a Date type for your group by.

GROUP BY CAST(myDateTime AS DATE)

Upvotes: 525

Related Questions