Case function in T-SQL

Wondering if possible to use case function in where clause of T-SQL statement. I have this application that needs to get the recordsets based on the day of the date selected that is @.dDay in one of these set of values {0,7,14,21, more). Though, I know that if I use either of this:

datediff(dd,@.startdate,@.today) = @.dDay

or convert(varchar(12), @.startDate, 101) between convert(varchar(12), @.today, 101) and convert(varchar(12), dateadd(dd,@.dDay, @.today), 101)

It will work for only the values that are specific as in number but what of if "more" is selected which means that from that day upward, can i use this T-SQL to accomplish this. PLease help

Any suggestion is welcome. thanks

I think I got confused.. can you explain a bit more..?

|||

i too am confused by what you want but just keep in mind that if you include a function your where clause you will likely disallow the use of indexes on your date field, if there is one

|||

 
SELECT *FROM tblWHERE StartDate>=DATEADD(DAY,DATEDIFF(DAY, 0, @.today), 0)AND StartDate<CASEWHEN @.dday='more'THEN'9999-12-31'ELSEDATEADD(DAY,DATEDIFF(DAY, 0, @.today), @.dday+1)END
 

|||

I mean this; I have a textbox with a dropdownlist control to search my databasein which the user can select either "Today", "7 days", "14 days", and "More". If a user select either of the list item except "More", i think i can easily use the"between" and"and" to get the recordsets or usedatediff function to get the recordsets, but what of if the user select"more" which is not bounded but means "from that day and on", how can one control this by using the same T-SQL statement to return the recordset from the database. I am not ad-hoc method to access my datastore but stored procedure.

So i am looking at usingcase function to make the decision and return the recordsets. Is it possible or is there any simpler way?.

Please Help. Urgent

|||

If I understand you correctly, I would use a second stored procedure for the "More" case. IOW,

select @.SomeDate = datediff(....)

then

select ...... from..... where DateField < @.SomeDate

If the user selects a specific number of days, select proc1, else select proc2

Another option is to use a big if statement with 2 selects (to handle either case), but that won't optimize as well

|||

If that in the case, I would change the listitem in the dropdown with the "more" text to have an extremely large value, like say 100000 (250ish years). Then you don't have to have anything complicated on the SQL side. That way you can still have "more" displayed in the listbox, but when it actually goes to send it to the SqlDatasource, it will send 100000 instead of the word more.