SQLTeam.com | Weblogs | Forums

CASE in WHERE

Hello,

I want to select data depending on the weekday. Basically weekday +1. In case it is a Friday weekday + 3 to see Monday's data.

Error message: "Incorrect syntax near '<'."

Select * From ...
where

case
when datepart(weekday, getdate()) = '5' then
a.Service_date < convert(date, GETDATE() + 3, 112)
AND (STF_Status is null OR STF_Status = 'empty')

else
a.Service_date = getdate() < convert(date, GETDATE(), 112)
AND (STF_Status is null OR STF_Status = 'empty')

end

You would be better joining to a calendar table as that would take into account public holidays.

Oh, I'm afraid you are right. Hmm, I was just about to solve it

Select * From ...
where
a.Service_date <
(case
when datepart(weekday, getdate()) = '5' then
convert(date, GETDATE() + 4, 112)
else
convert(date, GETDATE() + 2, 112)
end)

AND (STF_Status is null OR STF_Status = 'empty')

Don't use DATEPART for this because it's affected by the @@DATEFIRST setting. The code below works correctly 100% of the time under all date and/or language settings, i.e., @@DATEFIRST doesn't affect the code at all.

(STF_Status is null OR STF_Status = 'empty') AND
a.Service_date < dateadd(day, case when datediff(day, 4, getdate()) % 7 = 0 then 3 else 0 end, cast(GETDATE() AS date))
1 Like

Thanks Scott! It works and gives me new ideas..