How to get all records for the current day.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to get all records for the current day.

Author

Topic

budbjames
Starting Member

1 Post

Posted - 2007-12-23 : 22:17:24

Hello all,

First time posting to the forum.

I have a table that tracks each vist to a page.

I have a query that tells me how many unique IP's have visited the site within the past 24 hours. This is too specific. I would rather adjust the query to show me all records for the current day.

Currently if it's 12/23/2007 4:00pm, my query checks between 12/22/2007 4:00pm - 12/23/2007 4:00pm.

I'd like it to be if it's 12/23/2007 it will show all records on the 23.

I can't do WHERE DateField = getdate() because it will use the time stamp as well..

Here is my query as is

SELECT Count(DISTINCT IPAddress) FROM VisitStatistics WHERE (VisitTime BETWEEN DATEADD(day, -1, getdate()) AND getdate());

Any suggestions?

Thanks.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-23 : 22:51:29

Did you get correct result with that query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-24 : 01:02:27

Try this:-
SELECT Count(DISTINCT IPAddress) FROM VisitStatistics WHERE (DATEADD(d,DATEDIFF(d,0,VisitTime),0) = DATEADD(d,DATEDIFF(d,0,getdate()),0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-24 : 01:49:53

or

where datecol>=dateadd(day,datediff(day,0,getdate()),0)
and datecol<dateadd(day,datediff(day,0,getdate()),1)

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-26 : 21:49:22

quote:
Originally posted by visakh16

Try this:-
SELECT Count(DISTINCT IPAddress) FROM VisitStatistics WHERE (DATEADD(d,DATEDIFF(d,0,VisitTime),0) = DATEADD(d,DATEDIFF(d,0,getdate()),0)

Absolutely not... that won't allow Index Seeks if an index is available. Madhivinan did it the right way to allow Index Seeks.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources