Solved

SQL Date query to calculate the day before

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2008 R2 calc date formula 3 28
SQL VIEW 7 25
Sql Query with datetime 3 14
sql server group by every 2hrs 5 21
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

863 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

23 Experts available now in Live!

Get 1:1 Help Now