Case in Where Clause - 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

Case in Where Clause

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2015-02-05 : 06:11:14

Hi,

i Have the following SQL Statement



Select * from dtree 


WHERE Subtype=848 AND



(MODIFYDATE between to_date(%2,'dd/mon/yyyy HH24:MI:SS') AND to_date(%3,'dd/mon/yyyy HH24:MI:SS') )

the numnbers %1, %2, %3 are variables and they get the values from another application. It works so far, but there case I can't handle.
If the date values (%2,%3) are empty.I want that my query finds everthing without considering the Date condition...

I tried it with a CASE condition:





Select * from dtree 


WHERE Subtype=848 AND


AND 

(MODIFYDATE between to_date(CASE  LEN(%2)=0 THEN '03/Feb/2015 00:00:00' END,'dd/mon/yyyy HH24:MI:SS') AND to_date(CASE  LEN(%3)=0 THEN '05/Feb/2015 00:00:00' END,'dd/mon/yyyy HH24:MI:SS') )

but it doesn't work. Has anybody an idea how I can handle emty values?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-05 : 08:49:43

First off, a variable name must begin with an ampersand (@) not a percent.
Second, there is no to_date function in SQL Server. Do you mean:


cast(case ... end as datetime)

Third, f by 'empty' you mean null (assuming MODIFYDATE is defined as datetime or smalldatetime).


case when @1 is null then ... end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources