number of events between two dates

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 2008 Forums

Transact-SQL (2008)

number of events between two dates

Author

Topic

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-04-03 : 12:09:01

This should return 2 as there are 2 events between todays date and the date 12 months befor today. It returns 0. I think the issue is because the column type of EventDate is nchar. Is this correct?


DECLARE @y date
DECLARE @d date

SET @y = DATEADD(m, -12, CURRENT_TIMESTAMP)
SET @d = CAST(GETDATE() AS date) 

SELECT COUNT(*)
FROM tbl_HAW_HealthAndSafety_LRI
WHERE EventDate BETWEEN @y AND @d

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-04-03 : 12:18:01

WHERE Convert(date, EventDate) BETWEEN @y AND @d

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-04-03 : 12:30:14

Thanks for the help. Still gives me zero. Any other ideas? I might be wrong on the type.

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-03 : 12:51:32

Show us some of the values

SELECT TOP 10 EventDate FROM tbl_HAW_HealthAndSafety_LRI

And post what that produces please

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-04-03 : 14:49:08

OK stupidity proven. I hadn't put data in the table after deleting first thing this morning.....is it a weekend yet? The above answer did work. tHANKS SO MUCH FOR THE HELP.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-04-03 : 16:01:00

LOL.

Glad to help

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-04-03 : 16:34:03

We've all been there. QED

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources