If this is your first visit, check our documentation. To post, register or login.
Forums / Currencies & Sales Taxes, VAT, GST, etc. / SQL: Rounding Up All prices to nearest pound

SQL: Rounding Up All prices to nearest pound

Started by meltdown on 6 Jun 2012, 8:20 AM · Views: 1,779

Results 1 to 3 of 3
6 Jun 2012, 8:20 AM
#1
meltdown avatar

meltdown

Totally Zenned

Join Date:
Jun 2006
Location:
My family and I live in Brighton, England
Posts:
947
Plugin Contributions:
0

SQL: Rounding Up All prices to nearest pound

I have a multiple-currency site. I've just physically transferred from a Euro country to the UK. I successfully used the following sql to update my site prices according to the current exchange rate at that time:

update products set products_price = products_price * '0.83870798';

However, my default currency prices are now a bit messy, i.e. £3.61 or £4.39 (I probably should have used 0.85 instead of 0.83870798 in the sql). Is it possible to use a similar sql to round up all of the default currency prices to the nearest pound?

Thanks!!

6 Jun 2012, 6:38 PM
#2
niccol avatar

niccol

Totally Zenned

Join Date:
Apr 2009
Posts:
2,138
Plugin Contributions:
1

Re: SQL: Rounding Up All prices to nearest pound

I think that:

update products set products_price = ROUND(products_price);

should do it but it is untested. Copy your database and try it on the copy first :-)

otherwise have a look at:

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

(re-reading your post you probably want to use ceiling rather than round)

Nick
iszent.com

9 Jun 2012, 8:20 AM
#3
meltdown avatar

meltdown

Totally Zenned

Join Date:
Jun 2006
Location:
My family and I live in Brighton, England
Posts:
947
Plugin Contributions:
0

Re: SQL: Rounding Up All prices to nearest pound

niccol:

I think that:

update products set products_price = ROUND(products_price);

should do it but it is untested. Copy your database and try it on the copy first :-)

otherwise have a look at:

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

(re-reading your post you probably want to use ceiling rather than round)

Perfect. Thanks for that, it was EXACTLY what I was looking for. Taking your advice, I used:

update products set products_price = CEILING(products_price);

I successfully introduced it into a number of Zen Carts and the following versions: 1.3.6 & 1.3.8a.

Thank again :)