asked on

SQL Date query to calculate the day before

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')

ksaul

SELECT COUNT(DISTINCT SOURCE_IP) AS [IP ADDR]
FROM INCOMING_VIEW
WHERE DAY(DATE_TIME) = DAY(GetDate() - 1)

ASKER CERTIFIED SOLUTION

jhshen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

markodenev

ASKER

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.

Chris McGuigan

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