Select all 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 2000 Forums

SQL Server Development (2000)

Select all events between two dates

Author

Topic

jgd12345
Starting Member

6 Posts

Posted - 2005-12-13 : 05:02:51

Hi, I'm doing an events calendar and I need to select all the events from the database where the events takes place in the current month. My problem lies in that I have a start date and end date. Here's what I've got so far:

SELECT * FROM dbo.qryEventsPlusFE WHERE month(fldStartDate) <= month(getdate()) AND year(fldStartDate) <= year(getdate()) AND month(fldEndDate) >= month(getdate()) AND year(fldEndDate) >= year(getdate())

but it does not select the records where the month of the start date is < current month and the month of the end date is > current month.

I'd appreciate it if someone could help. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-13 : 05:11:01

First of all Welcome to SQLTeam !

You can use convert(char(6), yourdate, 112) to convert your date to YYYYMM and use it to compare.

select * 
from  dbo.qryEventsPlusFE
where convert(char(6), fldStartDate, 112) <= convert(char(6), getdate(), 112)
and   convert(char(6), fldEndDate,   112) >= convert(char(6), getdate(), 112)

When comparing date (in your case year & month only) you should always compare the year and month together rather than what you did. You will have problem with date like
end date = 2006-Jan
current date = 2005-Dec
Year (end date) > year (current date)
but month (end date) will be smaller than month (current date)
-----------------
[KH]
Learn something new everyday

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 06:16:27

[code]
DECLARE @MyStartDate datetime,
@MyEndDate datetime
SELECT @MyStartDate = '01Dec2005',
@MyEndDate = '01Jan2006'

select *
from dbo.qryEventsPlusFE
WHERE fldStartDate < @MyEndDate
AND fldEndDate >= @MyStartDate
[/code]
This should find events that start before the 1st of NEXT month and finish on, or after, the 1st of THIS month. That will include things that start before this month and finish after it - i.e. are in progress for the whole month.

If you are happy with that then you can make @MyStartDate and @MyEndDate dynamic with the slightly convoluted code:
[code]
SELECT @MyStartDate = DATEADD(Month, DATEDIFF(Month, 0, GetDate()), 0)
SELECT @MyEndDate = DATEADD(Month, 1, @MyStartDate)
[/code]
Kristen

jgd12345
Starting Member

6 Posts

Posted - 2005-12-13 : 06:37:23

Genius, much appreciate. I went for khtan's method but both worked a treat. Cheers.

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 08:40:27

I'm sure khtan is a Jolly Fine Chap! but his method will not use any indexes on the table, and thus will be slower than mine. So There!

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-13 : 20:40:11

quote:
Originally posted by Kristen

I'm sure khtan is a Jolly Fine Chap! but his method will not use any indexes on the table, and thus will be slower than mine. So There!

Kristen

Yes Kristen. You have a point there.

jgd, Using 2 extra variable as what Kristen will give u extra performance

-----------------
[KH]
Learn something new everyday

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources