Solved

# SQL Date query to calculate the day before

Posted on 2007-04-04
3,645 Views
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
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

LVL 10

Expert Comment

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

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

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

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

Question has a verified solution.

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

### Suggested Solutions

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.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month7 days, 7 hours left to enroll