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

x
?
Solved

SQL Date query to calculate the day before

Posted on 2007-04-04
4
Medium Priority
?
3,659 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
[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 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 2000 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 To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

721 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