# Update the Price field and format digits after decimal point

I have a Price field that has values like :
12.18, 45.78, 9.66, etc...
I want to Update the Price field and format digits after decimal point.

I want the last 2 digits after the decimal point to be rounded like this :

If :  .00 to .29  =  .29

If :  .30 to .49  =  .49

If :  .50 to .69  =  .69

If :  .70 to .99  =  .95

So if  the price is "29.14", it would be "29.29", . . . "18.78" would be "18.99", etc.

UPDATE Nop_ProductVariant
SET Price =

How can I do this? thanks
LVL 16
###### Who is Participating?

Hi,

check out this.
It might helps.

- Bhavesh
``````declare @var numeric(30,2)
set @var = 15.78
select case when @var-floor(@var) >= 0.70 then floor(@var) + 0.95
else case when @var-floor(@var) >= 0.50 then floor(@var) + 0.69
else case when @var-floor(@var) >= 0.30 then floor(@var) + 0.49
else case when @var-floor(@var) >= 0 then floor(@var) + 0.29
end end end end as Price
``````
0

Commented:
You can also try this and see if it helps:

update Nop_ProductVariant
set price = case when price between 19.00 and 29.14 then LEFT(CONVERT(VARCHAR(20),price),2)+'.29' end)

If that works, then add more conditions like:

when price between 0.00 and 18.78 then LEFT(CONVERT(VARCHAR(20),price),2)+'.99' end)

0

Author Commented:
thanks Brichsoft  .. that's perfect.

How do I use it in the Update statement ?

UPDATE Nop_ProductVariant
SET Price =

select case when @var-floor(@var) >= 0.70 then floor(@var) + 0.95
else case when @var-floor(@var) >= 0.50 then floor(@var) + 0.69
else case when @var-floor(@var) >= 0.30 then floor(@var) + 0.49
else case when @var-floor(@var) >= 0 then floor(@var) + 0.29
end end end end as Price
0

Author Commented:
I'm not good with the CASE statement, but I think this worked :

UPDATE Nop_ProductVariant
SET Price =
CASE WHEN Price-floor(Price) >= 0.70 THEN floor(Price) + 0.95 ELSE
CASE WHEN Price-floor(Price) >= 0.50 THEN floor(Price) + 0.69 ELSE
CASE WHEN Price-floor(Price) >= 0.30 THEN floor(Price) + 0.49 ELSE
CASE WHEN Price-floor(Price) >= 0 THEN floor(Price) + 0.29
END END END END

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.