Solved

help with SQL statement

Posted on 2009-05-04
7
305 Views
Last Modified: 2012-05-06
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
Comment
Question by:Cerixus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24296084
Do you want all the dates before yesterday and all the dates after today?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24296113
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24296117
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 1

Author Comment

by:Cerixus
ID: 24296118
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24296137
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
 
LVL 1

Author Comment

by:Cerixus
ID: 24296218
ExportTime < DATEADD(day, -1, DATEDIFF(day, 0, GETDATE()))

Worked perfectly.  Thanks!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24296250
Cerixus,

Glad that helped!

Happy coding...

Regards,
Kevin
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question