Date Range Query - 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 2005 Forums

Transact-SQL (2005)

Date Range Query

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2008-03-07 : 08:44:18

Hello,

I have a table called Event, this table has two fields called StartDate and FinishDate. On my application I want the user to be able to search for all the events for a certain date range. I am having some trouble getting my head around the code and was wondering if someone could offer some advice.

Some more details... the user can enter a from date, a to date or both. I can have separate sql code for each situation. The event.startdate and event.finishdate are never null and may contain a time value.

Thanks.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-07 : 08:58:54

try this;

DECLARE @StartDate DATETIME,
@EndDate DATETIME

SELECT @StartDate = '2007-08-27 14:47:42.897',
@EndDate = '2007-08-24 15:19:55.933'

SELECT *
FROM <tbl>
WHERE ( @StartDate IS NULL OR DATEADD(DAY, 0, DATEDIFF(DAY, 0, StartDate)) >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, @StartDate)))
AND (@EndDate IS NULL OR DATEADD(DAY, 0, DATEDIFF(DAY, 0, FinishDate)) >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, @EndDate)))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-07 : 09:45:07

Does the user input FROM and TO date contains time as well ?

KH
[spoiler]Time is always against us[/spoiler]

leahsmart
Posting Yak Master

133 Posts

Posted - 2008-03-07 : 10:00:22

No the date ranges provided by the user to search by just contain the date.

Thanks for your reply PeterNeo, I am having some trouble understanding it at the moment, I'll have a good look at it later today :D

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-07 : 10:54:14

What is the search criteria ? does both event start and end date must be between the input FromDate and EndDate ?

Depending on your defined search rules, the query will be different. Here is an example to give you a start


select *
from   Event
where  StartDate    >= @FromDate
and    FinishDate   <  dateadd(day, 1, @ToDate)

KH
[spoiler]Time is always against us[/spoiler]

leahsmart
Posting Yak Master

133 Posts

Posted - 2008-03-10 : 11:15:55

Heya,

Thanks for the replies, I got it working using the following code:

(
	(@MinDate IS NOT NULL AND @MaxDate IS NULL AND EventFinishDate >= @MinDate) 
	OR 
	(@MinDate IS NULL AND @MaxDate IS NOT NULL AND EventStartDate <= @MaxDate) 
	OR 
	(@MinDate IS NOT NULL AND @MaxDate IS NOT NULL AND 
		( 
			(EventStartDate BETWEEN @MinDate AND @MaxDate) 
		OR 
			(EventFinishDate BETWEEN @MinDate AND @MaxDate) 
		OR 
			(EventStartDate <= @MinDate AND EventFinishDate >= @MaxDate) 
		) 
	)
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-10 : 11:21:31

You really meant this, right?

SELECT	*
FROM	Table1
WHERE	EventStartDate <= COALESCE(@MaxDate, '99991231')
	AND EventFinishDate >= COALESCE(@MinDate, '17530101')

E 12°55'05.25"
N 56°04'39.16"

leahsmart
Posting Yak Master

133 Posts

Posted - 2008-03-10 : 11:38:03

quote:
Originally posted by Peso

You really meant this, right?
SELECT *
FROM Table1
WHERE EventStartDate <= COALESCE(@MaxDate, '99991231')
AND EventFinishDate >= COALESCE(@MinDate, '17530101')
E 12°55'05.25"
N 56°04'39.16"

Nope, no idea what that is or what it is doing. It hurts my brain.

leahsmart
Posting Yak Master

133 Posts

Posted - 2008-03-10 : 11:44:10

quote:
Originally posted by Peso

You really meant this, right?
SELECT *
FROM Table1
WHERE EventStartDate <= COALESCE(@MaxDate, '99991231')
AND EventFinishDate >= COALESCE(@MinDate, '17530101')
E 12°55'05.25"
N 56°04'39.16"

Wow it works.... how can it be. I am soo confused

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-10 : 11:46:11

quote:

Originally posted by leahsmart

(
	(@MinDate IS NOT NULL AND @MaxDate IS NULL AND EventFinishDate >= @MinDate)	-- Covered in my second line
	OR 
	(@MinDate IS NULL AND @MaxDate IS NOT NULL AND EventStartDate <= @MaxDate)	-- Covered in my first line

	OR 
	(@MinDate IS NOT NULL AND @MaxDate IS NOT NULL AND 
		( 
			(EventStartDate BETWEEN @MinDate AND @MaxDate)			-- Covered (with last OR) in my first line
		OR 
			(EventFinishDate BETWEEN @MinDate AND @MaxDate)			-- Covered (with last OR) in my second line
		OR 
			(EventStartDate <= @MinDate AND EventFinishDate >= @MaxDate)	-- See previous two responses
		) 
	)
)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources