CODEWITHSUNDEEP
sqldatetime
jpavlov
jpavlov

Reputation: 2261

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

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

Upvotes: 1

Views: 1803

Answers (4)

René Wolferink
René Wolferink

Reputation: 3548

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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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').

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146603

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

Upvotes: 0

Taryn
Taryn

Reputation: 247880

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

Upvotes: 4

Related Questions