Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

help with SQL statement

Posted on 2009-05-04
7
Medium Priority
?
315 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

715 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