Solved

SQL Date query to calculate the day before

Posted on 2007-04-04
4
3,614 Views
Last Modified: 2008-01-09
Dear Experts,
I would like to schedule the following query to be run on a daily (morning) bases rather then me running it manually, but what I would like it to do is calculate the date back to yesterday.
SELECT     COUNT(DISTINCT SOURCE_IP) AS [IP ADDR]
FROM         INCOMING_VIEW
WHERE     (DATE_TIME > '2007-04-03 00:00:00') AND (DATE_TIME < '2007-04-03 23:59:59')
0
Comment
Question by:markodenev
4 Comments
 
LVL 10

Expert Comment

by:ksaul
ID: 18855258
SELECT     COUNT(DISTINCT SOURCE_IP) AS [IP ADDR]
FROM         INCOMING_VIEW
WHERE     DAY(DATE_TIME) = DAY(GetDate() - 1)
0
 
LVL 2

Accepted Solution

by:
jhshen earned 500 total points
ID: 18855296
Here are two ways to do so.
1.
SELECT     COUNT(DISTINCT SOURCE_IP) AS [IP ADDR]
FROM         INCOMING_VIEW
WHERE     (DATE_TIME >= dateadd(day, -1, convert(datetime, convert(varchar, getdate(), 104), 104))) AND (DATE_TIME < convert(datetime, convert(varchar, getdate(), 104), 104))

2.
SELECT     COUNT(DISTINCT SOURCE_IP) AS [IP ADDR]
FROM         INCOMING_VIEW
WHERE     DAY(DATE_TIME) = DAY(GetDate() - 1)
And MONTH(DATE_TIME) = MONTH(GetDate() - 1)
And YEAR(DATE_TIME) = YEAR(GetDate() - 1)
0
 

Author Comment

by:markodenev
ID: 18855589
The two optionts provided by jhshen are the ones that worked correctly, but if I was to perform this to greater then one day back then the only query that gave me the same results as my quary was option 2 from jhshen. So thanks to all that participated.
0
 
LVL 18

Expert Comment

by:chrismc
ID: 18855696
The following will also work, and might be slightly more efficient.
The convert strips the time off the dates and DateAdd takes a day off of todays date. DateAdd is a very useful function.
Code 112 sets dates into ISO mode (yyyymmdd) which is a more compact format for internal calculations of this nature. See the SQL help on Convert for details of date format codes.

SELECT     COUNT(DISTINCT SOURCE_IP) AS [IP ADDR]
    FROM         INCOMING_VIEW
    WHERE     Convert(VarChar(8), DATE_TIME, 112) = Convert(Varchar(8), DateAdd(dd, -1, GetDate(), 112)

Cheers
Chris
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now