Filter date if not null - 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 2000 Forums

SQL Server Development (2000)

Filter date if not null

Author

Topic

veebster
Starting Member

3 Posts

Posted - 2005-01-24 : 20:47:58

I have a table of data that has two date fields amongst others

StartDate
EndDate

Usually they contain a null value but if one of them contains a date, how can I NOT include that particular record in a query if:

The start date is before the current date or the end data is after the current date

Many thanks

jhermiz

3564 Posts

Posted - 2005-01-24 : 21:34:25

quote:
Originally posted by veebster

I have a table of data that has two date fields amongst others

StartDate
EndDate

Usually they contain a null value but if one of them contains a date, how can I NOT include that particular record in a query if:

The start date is before the current date or the end data is after the current date

Many thanks

WHERE
(@StartDate > GetDate() --negates your condition
OR @EndDate < GetDate()) --negates your second condition

Taking the NOT of all that you get
@StartDate < GetDate() (start date is before the current date)
OR (your or condition) becomes AND
@EndDate > GetDate() (end date is after the current date)

So basically I just took what you said and negated it.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-01-24 : 21:36:51

Forgot to mention in your sproc make the parameters take NULLS as defaults:

@Startdate datetime=NULL,
@Enddate datetime=NULL

Then you can use boolean logic to see if a value exists:

YourTable.YourField=@StartDate OR @StartDate IS NULL

1 OR 1 = 1
1 OR 0 = 1
0 OR 1 = 1
0 or 0 = 0

In your case if you pass null to the start date the second portion (@StartDate IS NULL) holds true, else it has a date and the first portion YourTable.YourField=@StartDate holds true.

HTH

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources