Date selection or all 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

SQL Server 2005 Forums

Transact-SQL (2005)

Date selection or all dates

Author

Topic

cardgunner

326 Posts

Posted - 2011-09-22 : 07:44:04

I'm trying to set up some criteria in my select query where if the dates are specified then show those records if none are specified then show all the records

declare @s_date datetime
declare @e_date datetime
set @s_date=dateadd(day, -7, current_timestamp)
set @e_date=current_timestamp

select *
from table
where case when @s_date='' and @e_date='' then t_odat like '%'
else (t_odat between @s_date and @e_date) end

the above gets me an error incorrect syntax near the keyword 'like' however t_odat like '%' by itself works for "All records".

Any Suggestions?

CardGunner

bobmcclellan
Starting Member

46 Posts

Posted - 2011-09-22 : 08:51:22

how about...

select *
from table
where @s_date='' and @e_date=''

union

select *
from table
where t_odat between @s_date and @e_date

cardgunner

326 Posts

Posted - 2011-09-22 : 09:52:44

Thanks Bob.

Yes that will work however I would like to get the desired results without using a union.

CardGunner

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-09-22 : 09:59:06

try:


select * from table
where (@s_date is null or @e_date is null)
   or ((@s_date is not null and @e_date is not null) and t_odat between @s_date and @e_date)

No, you're never too old to Yak'n'Roll if you're too young to die.

cardgunner

326 Posts

Posted - 2011-09-22 : 10:26:06

Well it did work sortof. I had to change it to

where (@s_date='' or @e_date='')
   or ((@s_date!='' and @e_date !='') and t_odat between @s_date and @e_date)

Thanks for your help.

CardGunner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources