• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

help with SQL statement

The attached statement basically returns all dates in a datetime field.  I would like it to exclude today and yesterday.

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


edit: attached code looked weird, included here as well.
SELECT CONVERT(CHAR(10),ExportTime, 101) AS Expr1, COUNT(*) AS DumpCount FROM ServerData GROUP BY CONVERT(CHAR(10),ExportTime, 101) ORDER BY Expr1 ASC

Open in new window

0
Cerixus
Asked:
Cerixus
  • 4
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Do you want all the dates before yesterday and all the dates after today?
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This should help you out:
SELECT CONVERT(CHAR(10),ExportTime, 101) AS Expr1, COUNT(*) AS DumpCount 
FROM ServerData 
WHERE ExportTime <= CONVERT(datetime,CONVERT(char(10), ExportTime - 1, 101),101)
GROUP BY CONVERT(CHAR(10),ExportTime, 101) 
ORDER BY Expr1 ASC

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Try this:
SELECT CONVERT(CHAR(10),ExportTime, 101) AS Expr1, COUNT(*) AS DumpCount 
FROM ServerData
WHERE ExportTime < DATEADD(day, -1, DATEDIFF(day, 0, GETDATE()))
AND ExportTime >= DATEADD(day, 1, DATEDIFF(day, 0, GETDATE()))
GROUP BY CONVERT(CHAR(10),ExportTime, 101) 
ORDER BY Expr1 ASC

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CerixusAuthor Commented:
I want all the dates prior to yesterday, yes.  The field will not contain any dates after today (unless something very strange is going on) :)
0
 
Kevin CrossChief Technology OfficerCommented:
I meant for that to an OR:

SELECT CONVERT(CHAR(10),ExportTime, 101) AS Expr1, COUNT(*) AS DumpCount
FROM ServerData
WHERE ExportTime < DATEADD(day, -1, DATEDIFF(day, 0, GETDATE()))
OR ExportTime >= DATEADD(day, 1, DATEDIFF(day, 0, GETDATE()))
GROUP BY CONVERT(CHAR(10),ExportTime, 101)
ORDER BY Expr1 ASC

It would probably make sense that data doesn't have any dates after today, so can just be like this:

SELECT CONVERT(CHAR(10),ExportTime, 101) AS Expr1, COUNT(*) AS DumpCount
FROM ServerData
WHERE ExportTime < DATEADD(day, -1, DATEDIFF(day, 0, GETDATE()))
GROUP BY CONVERT(CHAR(10),ExportTime, 101)
ORDER BY Expr1 ASC
0
 
CerixusAuthor Commented:
ExportTime < DATEADD(day, -1, DATEDIFF(day, 0, GETDATE()))

Worked perfectly.  Thanks!
0
 
Kevin CrossChief Technology OfficerCommented:
Cerixus,

Glad that helped!

Happy coding...

Regards,
Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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