Solved

SQL Date query to calculate the day before

Posted on 2007-04-04
4
3,640 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

How to increase the row limit in Jasper Server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

679 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