SQL - Start Date/End Date for current month

[waiting12345]

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

 

[bboffin]

Try:

SELECT * FROM dbo.qryEventsPlusFE 
WHERE month(getdate()) BETWEEN month(fldStartDate) AND month(fldEndDate)
AND year(getdate()) BETWEEN year(fldStartDate) AND year(fldEndDate)

Bob Boffin

 

[waiting12345]

Hi, nope it gave me the exact same resuls. Cheers anywayz.

 

[earthandfire]

You could try something like

declare @tbl table (fldID int, fldStartDate datetime, fldEndDate datetime)
insert into @tbl values(1, '20051110', '20051212')
insert into @tbl values(2, '20051111', '20051114')
insert into @tbl values(3, '20051210', '20051212')
insert into @tbl values(4, '20051211', '20060114')

declare @first datetime
declare @last datetime
set @first = dateadd(d, -day(getdate()), getdate()) + 1
set @last = dateadd(m, 1, @first)

select * 
from @tbl 
where (fldStartDate >= @first and fldStartDate < @last) or 
			(fldEndDate >= @first and fldEndDate < @last)

-- or without using @first and @last ...

select * 
from @tbl 
where (fldStartDate >= (dateadd(d, -day(getdate()), getdate()) + 1) and 
				fldStartDate < dateadd(m, 1, (dateadd(d, -day(getdate()), getdate()) + 1))) 
		or 
			(fldEndDate >= (dateadd(d, -day(getdate()), getdate()) + 1) and 
				fldEndDate < dateadd(m, 1, (dateadd(d, -day(getdate()), getdate()) + 1)))

Hope this helps

 

[earthandfire]

Sorry, I posted the wrong example. Try this one:

declare @tbl table (fldID int, fldStartDate datetime, fldEndDate datetime)
insert into @tbl values(1, '20051110', '20051212')
insert into @tbl values(2, '20051111', '20051114')
insert into @tbl values(3, '20051210', '20051212')
insert into @tbl values(4, '20051111', '20060114')

declare @first datetime
declare @last datetime
set @first = dateadd(d, -day(getdate()), getdate())+ 1
set @last = dateadd(m, 1, @first)

select * 
from @tbl 
where (fldStartDate < @last and fldEndDate >= @first)

-- or without using @first and @last ...
select * 
from @tbl 
where (fldStartDate < (dateadd(m, 1, dateadd(d, -day(getdate()), getdate())+ 1)) and 
fldEndDate >= (dateadd(d, -day(getdate()), getdate())+ 1))

Hope this helps.

 

[vongrunt]

Very close - but @first still contains time fraction. Try:

set @first = dateadd(mm, datediff(mm, 0, getdate()), 0)

The rest of code unchanged...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[earthandfire]

Does the time fraction matter as I used >= ?

 

[vongrunt]

Yes - that code would ignore anything before Dec 1st <insert current time here>...



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[earthandfire]

Because GetDate returns the time as well as the date. I hadn't thought of that. Thanks.

 

[bboffin]

I forgot to allow for the end month being less than the start month over the end of the year.

Try this variation:

SELECT * FROM dbo.qryEventsPlusFE
where month(getdate()) + 12 * year(getdate()) between month(fldstartdate) + 12 * year(fldstartdate) and 
month(fldenddate) + 12 * year(fldenddate)

This works by simply calculating the number of months each date represents.

I've checked this out on some sample data and it works for me. Note that time portion of getdate() is irrelevant because the query only uses the month or year portion for comparison.

It also avoids the messy temporary table and should be pretty quick.

If the query is likely to run for some time it would be better to declare a datetime variable that is loaded with getdate() once at the start of the statement and then use that to guard against the remote possibility of you running this over midnight at the end of a month or year!


Bob Boffin