[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

SQL Query Help, group by date portion of datetime?

I have attached my current SELECT statement.  ExportTime is a datetime field.  It basically works, but I would like to see if there is any way to GROUP BY only the date portion of the datetime field.

For instance, if I had the following 3 records in the DB:
1/1/2005 12:12:40
1/1/2005 15:24:50
1/2/2005 08:13:08

It should return something like:
1/1/2005 - 2
1/2/2005 - 1
SELECT ExportTime, COUNT(*) AS DumpCount FROM ServerData GROUP BY ExportTime

Open in new window

0
Cerixus
Asked:
Cerixus
  • 4
  • 3
  • 2
  • +1
1 Solution
 
BrandonGalderisiCommented:
What db engine/version?  this will work on SQL Server
SELECT dateadd(d, datediff(d, 0, ExportTime),0) exporttime, COUNT(*) AS DumpCount FROM ServerData GROUP BY dateadd(d, datediff(d, 0, ExportTime),0) 

Open in new window

0
 
Nathan RileyFounder/CTOCommented:
Try this:
SELECT CONVERT(varchar(8), ExportTime, 112), COUNT(*) AS DumpCount 
FROM ServerData 
GROUP BY ExportTime

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope this helps you out:
SELECT CONVERT(CHAR(10),ExportTime, 101), COUNT(*) AS DumpCount 
FROM ServerData GROUP BY CONVERT(CHAR(10),ExportTime, 101)

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CerixusAuthor Commented:
Well, it automatically changed the query to the attached (basically just added "AS Expr1", but the results still aren't grouped correctly.  Here is an excerpt of the output:

20090325      1
20090415      84
20090415      1
20090415      75

I tried GROUP BY Expr1, but I get an error that it's an invalid column name.
SELECT     CONVERT(varchar(8), ExportTime, 112) AS Expr1, COUNT(*) AS DumpCount
FROM         ServerData
GROUP BY ExportTime

Open in new window

0
 
Nathan RileyFounder/CTOCommented:
How is it not grouped correctly?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try out my query
0
 
CerixusAuthor Commented:
@rrjegan17

That statement works perfectly as a query in SQL Studio, but since it doesn't return it as a column name I cannot use it in my VB project.  Changing it to:

SELECT CONVERT(CHAR(10),ExportTime, 101) AS Expr1, COUNT(*) AS DumpCount
FROM ServerData GROUP BY CONVERT(CHAR(10),ExportTime, 101)


Does not have the desired results either.
0
 
CerixusAuthor Commented:
Scratch that, I think it did work I just had a problem with my chart or something...
0
 
BrandonGalderisiCommented:
Did you try my query?
0
 
CerixusAuthor Commented:
Sorry, somehow when I came back to this thread the first time I only saw Gallitins answer.
0
 
BrandonGalderisiCommented:
Not sure how since I posted 2 minutes before the accepted solution.  Does it work?  If so, it should be re-opened and closed appropriately.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now