I am trying to update a field.
The field should be updated only if the current month is x and field_04 contains value y.
When I use a single condition the field gets updated just fine.
but I can't seem to get it to function when I add another condition.
This works:
update Items
set Field_04 = case
when DATEPART(mm,getdate()) = 1 and Field_04<'3500' then '3500'
when DATEPART(mm,getdate()) = 2 and Field_04<'4500' then '4500'
end
from Items
where DATEPART(mm,getdate()) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) and Condition='A'
This doesn't work:
update Items
set Field_04 = case
when DATEPART(mm,getdate()) = 1 then '3500'
when DATEPART(mm,getdate()) = 2 then '4500'
end
from Items
where DATEPART(mm,getdate()) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) and Condition='A'
I guess it has something to do with the when then statement.
Maybe it cannot contain multiple conditions or maybe I'm just using the wrong syntax?
Thanks in advance!
Clyde.
select field_04, date_part(mm, getdate()), case
when DATEPART(mm,getdate()) = 1 and Field_04<'3500' then '3500'
when DATEPART(mm,getdate()) = 2 and Field_04<'4500' then '4500'
else field_04
end as expr
from items where Condition = 'A'
Further you should always provide an else - else the result is NULL if no condition is met.