Compare dates - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Compare dates

Author

Topic

dimoss
Yak Posting Veteran

52 Posts

Posted - 2007-04-09 : 03:35:54

Hi,

I am new to SQL and I have a problem to deal with dates.
I have a field called LogDate (smalldatetime) and default value getdate(). The date is added immediately when a visitor visits a page of my site.

The problem is that I cannot retrieve the no of the visitors for today using getdate(). I use the following query but I am sure that something missing.

SELECT Count(*) As DailyVisitors
FROM stats
WHERE LogDate = CONVERT(varchar(10), GETDATE(), 103)

Please help

Denis

www.tabletennis.gr

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 03:37:42

[code]
SELECT Count(*) As DailyVisitors
FROM stats
WHERE LogDate >= dateadd(day, datediff(day, 0, getdate()), 0)
and LogDate < dateadd(day, datediff(day, 0, getdate()), 1)
[/code]

dimoss
Yak Posting Veteran

52 Posts

Posted - 2007-04-09 : 03:42:05

Wow! Fast reply!!
Thank you so much!!

www.tabletennis.gr

dimoss
Yak Posting Veteran

52 Posts

Posted - 2007-04-09 : 03:49:35

Something else..If I want to GROUP BY the ip address and get the unique visitors I use the following according to what you wrote BUT for some reason it returns all the records.

SELECT COUNT(*) AS DayUnique
FROM (SELECT LogRemote_Addr
FROM stats
GROUP BY LogRemote_Addr, LogDate
HAVING LogDate >= dateadd(day, datediff(day, 0, getdate()), 0) AND LogDate < dateadd(day, datediff(day, 0, getdate()), 1)) DERIVEDTBL

Any ideas?
Thanks.

www.tabletennis.gr

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 03:52:36

because you are grouping by LogDate


SELECT COUNT(*) AS DayUnique
FROM 
(
    SELECT LogRemote_Addr
    FROM   stats
    WHERE  LogDate >= dateadd(day, datediff(day, 0, getdate()), 0) 
    AND    LogDate <  dateadd(day, datediff(day, 0, getdate()), 1)
    GROUP BY LogRemote_Addr
) DERIVEDTBL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 03:55:06

You can also do this


    SELECT count(distinct LogRemote_Addr)
    FROM   stats
    WHERE  LogDate >= dateadd(day, datediff(day, 0, getdate()), 0) 
    AND    LogDate <  dateadd(day, datediff(day, 0, getdate()), 1)