Solved

help with SQL statement

Posted on 2009-05-04
7
285 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
  • 4
  • 2
7 Comments
 
LVL 59

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 59

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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 59

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 59

Expert Comment

by:Kevin Cross
ID: 24296250
Cerixus,

Glad that helped!

Happy coding...

Regards,
Kevin
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 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