select between optional 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

select between optional dates

Author

Topic

leofer
Starting Member

4 Posts

Posted - 2011-09-07 : 04:16:43

I want to select records from a table, Target, where the TargetDate is between @fromDate and @toDate. However, either @toDate, @fromDate or both can be NULL.

So, I want to...
SELECT TargetName, TargetDate FROM Target

WHERE

and this is where the problem starts...

if @fromDate IS NULL
if @toDate IS NULL
--return all TargetDate
else
-- return all TargetDate up to @toDate
else
if @toDate IS NULL
-- return all TargetDate from @fromDate to now
else
-- return all TargetDate between @fromDate to @toDate

How should my WHERE look like?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 04:26:18

[code]SELECT TargetName, TargetDate FROM Target where (TargetDate between @fromdate and coalesce(@todate,getdate()) or @fromdate is null)[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

leofer
Starting Member

4 Posts

Posted - 2011-09-07 : 05:38:37

Thank you for your suggestion, but it doesn't seam to work.

if @fromDate is null, it doesn't care about @toDate
if @toDate is null, it's ok
if both is null, it omits the GetDate() (dates > today exists in the database), which is good, that's actually is what I want(!).

So, if @toDate is null, I want to return either all dates (if also @fromDate is null), including future dates, or dates > @fromDate (again, including future dates). But, if @fromDate is null while @toDate isn't, I still want all dates UP TO @toDate.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-07 : 05:58:50

declare @fromdate datetime
declare @todate datetime

if @fromdate is null
SET @fromdate = (select distinct MIN(date) from table)

if @todate is null
set @todate = (select distinct MAX(date) from table)

select * from table where date between @fromdate and @todate

Karthik
http://karthik4identity.blogspot.com/

leofer
Starting Member

4 Posts

Posted - 2011-09-07 : 06:41:40

Perfect! Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 07:01:56

quote:
Originally posted by leofer

Perfect! Thanks!

how does it give you records till current time when @todate is null? i thought that was what you asked for!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

leofer
Starting Member

4 Posts

Posted - 2011-09-07 : 08:46:21

quote:
Originally posted by visakh16

quote:
Originally posted by leofer

Perfect! Thanks!

how does it give you records till current time when @todate is null? i thought that was what you asked for!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes, sorry, my original specifications was wrong. When @toDate is null, I want every date from @fromDate/MIN(TargetDate) (depending on if @fromDate is null or not), witch includes future dates (I didn't think of those undtil I ran your solution, so I still owe you a big THANKS).

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 09:14:59

Doing two selects to get the MAX / MIN dates before the main query is expensive on CPU (and the DISTINCT is redundant)

Depending on indexes this may be fast enough:


select * from table 
where (@fromdate IS NULL OR date >= @fromdate)
  and (@todate IS NULL OR date <= @todate)

but you run the risk that the ANDs and ORs mean that the query optimiser doesn't choose the optimum plan.

Probably the most efficient would be to use dynamic SQL with sp_ExecuteSQL, provided you won't have a permissions problem (user will need SELECT permission on the table, rather than just EXECUTE on the Stored Procedure - if you are using a stored procedure)

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 09:17:12

Note that if your @todate is just a date, and your [date] column includes time, then instead of


date <= @todate

you need


date < one_day_after_@todate

so:


date < DATEADD(Day, DATEDIFF(Day, 0, @todate), 0)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-09-07 : 18:24:52

My $0.02

select *
from table
where TargetDate between coalesce(@fromdate, '00010101') and coalesce(@todate, '99991231') -- Removes the OR operator

=======================================
The first rule of Tautology Club is the first rule of Tautology Club. -xkcd, (Honor Societies)

Kristen
Test

22859 Posts

Posted - 2011-09-08 : 01:37:23

Its a good point B.K. Do you think pre-assigning the value to the @Parameter would be more likely to allow query optimizer to "sniff" the parameter?


SELECT @fromdate = coalesce(@fromdate, '00010101'),
       @todate = coalesce(@todate, '99991231') 

select * 
from table 
where TargetDate between @fromdate and @todate

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources