filter by dates - 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

General SQL Server Forums

New to SQL Server Programming

filter by dates

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-01-23 : 14:12:57

I want to filter a table for data between two dates.
Start Date and End Date are the two dates.
The query,
1. should not filter based on date if the dates are not provided.
2. Should give all records on or after the start date if only the start date is given.
3.Should give all records on or before the end date if only the end date is given.
4. Should give all records on or between the start and end dates if both dates are given.

I tried something like this.

@startDate datetime =NULL ,
@endDate datetime = NULL

AS
BEGIN SET NOCOUNT ON;

SELECT * FROM TABLE
WHERE ((TICKET.CREATED_DATE >=@startDate AND TICKET.CREATED_DATE<=@endDate ) OR (@startDate IS NULL)OR (@endDate IS NULL)OR(@startDate IS NULL AND @endDate IS NULL))

Not working could someone give me the correct query.
Thanks

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-23 : 14:40:40

[code]IF @startDate IS NOT NULL AND @endDate IS NOT NULL
SELECT * FROM TABLE
WHERE TICKET.CREATED_DATE BETWEEN @startDate AND @endDate
ELSE
SELECT * FROM TABLE[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-23 : 17:00:58

Try this:

SELECT *
FROM Table
WHERE 
	Date BETWEEN @StartDate AND @EndDate
	OR
	(
		@StartDate IS NULL
		AND @EndDate IS NULL
	)
	OR
	(
		Date > @StartDate
		AND @EndDate IS NULL
	)
	OR
	(
		Date < @EndDate
		AND @StartDate IS NULL
	)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-23 : 17:17:24

I think this will be the best performing:

where created_date >= isNull(@FromDate, created_date)
and created_date <= isNull(@ToDate, created_date)

Be One with the Optimizer
TG

CShaw
Yak Posting Veteran

65 Posts

Posted - 2008-01-25 : 01:37:50

Is it possible to add a clustered index to that column?

Chris Shaw
www.SQLonCall.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources