How do I group by date and not have the time being a factor?

Question

My DateCompleted return the date and the time. If I would like to group just by the date and ignore the time. How would I go about this?

SELECT  
  Cast(Jobs.DateCompleted AS VarChar) AS 'DateCompleted',
  SUM(Metrics.GB) AS GB, 
  SUM(Metrics.KB) AS KB
FROM Metrics 
INNER JOIN Jobs ON Jobs.JobId = Metrics.JobId 
INNER JOIN Projects ON Projects.ProjectId = Jobs.ProjectId 
INNER JOIN JobTypes ON JobTypes.JobTypeId = Jobs.JobTypeId
WHERE Jobs.DateCompleted BETWEEN '12/01/2012' AND '12/03/2012' 
GROUP BY Jobs.DateCompleted

http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype — bonsvr, Dec 04 '12 at 15:22

score 4 · Accepted Answer · answered Dec 04 '12 at 15:17

If you are using SQL Server, then you can convert the value as a varchar with a format that strips the time:

SELECT convert(char(10), Jobs.DateCompleted, 120) AS DateCompleted,
  SUM(Metrics.GB) AS GB, 
  SUM(Metrics.KB) AS KB
FROM Metrics 
INNER JOIN Jobs 
  ON Jobs.JobId = Metrics.JobId 
INNER JOIN Projects 
  ON Projects.ProjectId = Jobs.ProjectId 
INNER JOIN JobTypes 
  ON JobTypes.JobTypeId = Jobs.JobTypeId
WHERE Jobs.DateCompleted Between '12/01/2012' AND '12/03/2012' 
GROUP BY convert(char(10), Jobs.DateCompleted, 120)

If you are using SQL Server 2008+, then you can cast the value as a date:

SELECT cast(Jobs.DateCompleted as date) AS DateCompleted,
  SUM(Metrics.GB) AS GB, 
  SUM(Metrics.KB) AS KB
FROM Metrics 
INNER JOIN Jobs 
  ON Jobs.JobId = Metrics.JobId 
INNER JOIN Projects 
  ON Projects.ProjectId = Jobs.ProjectId 
INNER JOIN JobTypes 
  ON JobTypes.JobTypeId = Jobs.JobTypeId
WHERE Jobs.DateCompleted Between '12/01/2012' AND '12/03/2012' 
GROUP BY cast(Jobs.DateCompleted as date)

Note: I am assuming SQL Server based on your previous tags

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

Write an expression that Strips off the time portion, and group by this expression... In Ms SQL Server you can use DateAdd(dd, datediff(dd, 0, myDatetime), 0) Each RDBMS has its own functions or syntax for this. for Ms SQLServer see this SO question

edited May 23 '17 at 12:27

Community

1
1

answered Dec 04 '12 at 15:18

Charles Bretana

143,358
22
150
216

score 0 · Answer 3 · answered Dec 04 '12 at 15:20

The answer is that you convert the datetime to a date, either explicitly (if date is a supported data type) or implicitly (to a string).

The ANSI standard way is to do something like:

select extract(year from jobs.DateCompleted),
       extract(month from jobs.DateCompleted),
       extract(day from jobs.DateCompleted),
. . .
group by extract(year from jobs.DateCompleted),
       extract(month from jobs.DateCompleted),
       extract(day from jobs.DateCompleted)

But no on ever does that. In recent versions of SQL Server, you would use cast(jobs.DateCompleted as date). In MySQL, date(jobs.DateCompleted). In Oracle, I would do to_char(jobs.DateCompleted, 'YYYY-MM-DD').

score 0 · Answer 4 · answered Dec 04 '12 at 15:34

You haven't specified a rdbms, so here's a wild guess.

If you are using SQL Server, then you can group by datepart parts of your datetimes.

GROUP BY DATEPART(year, Jobs.DateCompleted),
         DATEPART(month, Jobs.DateCompleted),
         DATEPART(day, Jobs.DateCompleted)

How do I group by date and not have the time being a factor?

4 Answers4