search by date - 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

SQL Server 2000 Forums

Transact-SQL (2000)

search by date

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-05-27 : 00:29:14

In my main table storing members of the database, I store their registration date. I am trying to create a StoredProc that I can view the count of new users for each day. The problem is how can I have a WHERE clause against the "signupdate" for each DAY. I pass it the current time and it brings back nothing because it is looking for signups of the exact second. I'm thinking I could pass it a range of the current day from 12am to 1159pm but I would hope that there is a better way?

Thanks AGAIN,
mike

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-05-27 : 00:40:40

Hi

Use the DateDiff function and specify a day

i.e.

WHERE DateDiff(d, signupdate, GetDate()) = 0

hope that helps

Damian

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-05-27 : 01:32:18

THanks for the help. 1 problem tho, when this table was first designed I made the mistake of calling my actual date column "DATE"

Here is my SP

CREATE PROCEDURE select_count_newUsers
(
@joinDate datetime
)
AS

SET NOCOUNT ON

SELECT count (userID) AS newUsers FROM tblUserDetails WHERE DateDiff (@joinDate, Date, GetDate()) = 0
GO

I get the following error and can't seem to figure out why.

Server: Msg 1023, Level 15, State 1, Procedure select_count_newUsers, Line 9
Invalid parameter 1 specified for datediff.

Thanks again guys..

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-05-27 : 01:36:28

The problem is the syntax for DateDIff

put @JoinDate in place of GetDate() (getdate will show todays) and D (for days) in the first parameter.

Look up DateDiff in BOL for a full rundown

Damian

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-05-27 : 01:41:43

you da man!!!

I'd like to take this one step further, if possible.

I plan on running this query to pass it 30 dates, todays date, today's date -1, today's date -2. today's date -3, etc...

Do I have to run 30 recordsets? Is it possible to modify this SP in anyway to have it bring back a recordset of the 30 different values? Or should I just run it 30 times with 30 different values??

greatly appreciated,

mike

Edited by - mike123 on 05/27/2002 01:47:37

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-05-27 : 01:51:05

Easy, you just have to do

DateDiff(d, [Date], @joindate) <= 30

and do a

GROUP BY DatePart(d, [Date])

that will give you a count of signups for each day.

Damian

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-05-27 : 02:06:08

Thanks again Merkin, this is what I understood from what you said, but it is bringing back incorrect data. The number are too high.
Also, can I sort these by the date so I can loop through them properly? I owe ya one!! thanks once again

CREATE PROCEDURE select_count_newUsers
(
@joinDate datetime
)
AS

SET NOCOUNT ON

SELECT count(userID) AS newUsers FROM tblUserDetails WHERE DateDiff (d, [Date], @joinDate) <= 30 GROUP BY DatePart(d, [Date])

GO

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-05-27 : 02:17:46

Hi

This should make it clearer



SELECT DateDiff(d, [Date], @joindate) [Days Ago], 
DatePart(d, [Date]) [Day Of Month],
Count(*) [Count of Users]

FROM
	newUsers 

WHERE
	DateDiff(d, [Date], @joindate) <= 30

GROUP BY DateDiff(d, [Date], @joindate), DatePart(d, [Date])

ORDER BY DateDiff(d, [Date], @joindate)

Run that and it should answer it all. You can use the output of datediff in any order by or where clauses.

Damian

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-05-27 : 02:23:41

Works perfectly - THANK YOU!,

Thank you very much for your time and effort..

Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources