Struggling with date between query.

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

Struggling with date between query.

Author

Topic

louisl
Starting Member

2 Posts

Posted - 2011-02-17 : 08:07:54

I've hit a wall with this one so I hope someone can help.

I have items in the database with a 'start' and 'end' date eg.

id,name,start_date,end_date
1,aaa,2011-03-10 09:00:00,2011-03-17 17:30:00
2,bbb,2011-03-15 14:00:00,2011-03-20 18:00:00

The user can search using a form and needs to select a date range 'from' and 'to' date eg.
(DD/MM/YYYY)
from: 11/03/2011
to: 12/03/2011

The problem I have is if they select dates outside or on the start and end date the item is selected but if it's inside the items start and end date it's not getting selected. I need some help with the logic in my query I think i may be approching it wrong. This is being run in Classic asp in Access so I need a straight SQL solution.

AND (

EventDates.EventStartDate BETWEEN #11/03/2011 00:00:00# AND #12/03/2011 23:59:59#
OR
EventDates.EventEndDate BETWEEN #11/03/2011 00:00:00# AND #12/03/2011 23:59:59#
)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-17 : 08:19:40

[code]
SELECT *
FROM YourTable
WHERE end_date >= @FromDate
AND start_date <= @ToDate
[/code]

louisl
Starting Member

2 Posts

Posted - 2011-02-17 : 08:46:10

I went down that route earlier but I had it back to front!

Thanks for your help that's perfect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources