Need to use "IF" logic in my where clause

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

Need to use "IF" logic in my where clause

Author

Topic

bw_sql_greenhorn
Starting Member

3 Posts

Posted - 2010-06-29 : 17:27:28

I'm trying to write a sql query and I need use one of two fields in the where clause depending on a value of a parameter.

Basics of query

Select field1, field2
from table
where
field3 = 0
and
--this is where I need help
--the table i'm using has 2 date fields and I need to check
--to see that the date field is between a start and end date
--For this, I'll use Date1 and Date2 and DateFieldToUse = 1
--for the parm that says to use Date1 and =2 for Date2

if
@DateFieldToUse = 1
then
Date1 Between '01/01/2010' and '04/01/2010'
else
Date2 Between '01/01/2010/ and '04/01/2010'

Basically, I need to return the row if the date field the user wants to use is between a certain date range. Current it is one of 2 fields, but could expand to other fields as there are 5 date fields in each row.

Thanks for any help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-29 : 17:47:34

[code]SELECT Field1,
Field2
FROM Table
WHERE Field3 = 0
AND CASE @DateFieldToUse
WHEN 1 THEN Date1
ELSE Date2
END BETWEEN '01/01/2010' and '04/01/2010'[/code]

N 56°04'39.26"
E 12°55'05.63"

pduffin
Yak Posting Veteran

68 Posts

Posted - 2010-06-29 : 17:59:36

Select field1, field2
from table
where
field3 = 0
and
case @DateFieldToUse
when 1 then date1
when 2 then date2
when 3 then date3
else date4
end between '01/01/2010' and '04/01/2010'

pduffin
Yak Posting Veteran

68 Posts

Posted - 2010-06-29 : 18:00:47

beaten to the punch!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-30 : 02:58:03

Also make sure you use YYYYMMDD format to express dates
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

bw_sql_greenhorn
Starting Member

3 Posts

Posted - 2010-06-30 : 12:28:14

Worked great. Thanks for the assistance