Set floor/round value sql value

Question

I'm developing a bank system for Garry's Mod (GLua) using sql for it. I want to create a "profit" that adds a percentage to every player every minute: The sql query is this one:

UPDATE 
     darkrp_player 
SET 
     bank = bank * "..multiplier

This is what it returns when running that query on DBBrowser:

no such function: floor: UPDATE darkrp_player SET bank = floor(bank * 1.25)

multiplier var is (profit/100) + 1 profit is another var

So my problem is that everytime I run that query, every bank row is full of decimals, example:

profit = 25
--before query:
'bank' = 2
--after query:
'bank' = 2.5

My question is: How can I set floor to the value I'm setting to 'bank'? I could get everybody's 'bank' value and set it one by one, but that would be really really complicated... So, i'm looking something like

UPDATE 
     darkrp_player 
SET 
     bank = floor(bank * "..multiplier..")"

I'm sorry if you haven't understood something, you can ask me whatever related to my problem, I'll be glad to answer you. THANKS!

It returns to me: no such function: floor: UPDATE darkrp_player SET bank = floor(bank * 1.25) — Alex Paredes, Sep 14 '16 at 18:23
are you sure that is exactly what it says... floor is a function in sql -- what version are you using? — Hogan, Sep 14 '16 at 18:29
The sql servers is built-in a server of Garry'sMod (game) as a library. [GLua wiki SQL LIBRARY](https://wiki.garrysmod.com/page/Category:sql) — Alex Paredes, Sep 14 '16 at 18:39
@AlexParedes - it uses an sql engine called sqlite... so you can google that for documentation. — Hogan, Sep 14 '16 at 18:48
Okay, I didn't know it was called sqlite, I searched sqlite floor function and appeared what Denis Reznik showed me (CAST function). Thank you anyway. — Alex Paredes, Sep 14 '16 at 18:55

Denis Reznik · Accepted Answer · 2016-09-14T19:01:46.937

0

If there is no FLOOR function on that built-in server, try this:

SELECT CAST('67.896' AS int),  CAST('5.57' AS int)

edited Sep 14 '16 at 19:01

answered Sep 14 '16 at 18:40

Denis Reznik

964
5
10

The problem is that I don't want to SELECT, I want to SET – Alex Paredes Sep 14 '16 at 18:43
1

@AlexParedes -- this is an example -- try it if it works use it in the set. – Hogan Sep 14 '16 at 18:43
@AlexParedes UPDATE darkrp_player SET bank = CAST(CAST(bank * "..multiplier AS decimal) AS int) – Denis Reznik Sep 14 '16 at 18:45
It worked, thank you! I tried CONVERT but didn't work, thanks for showing me CAST function! – Alex Paredes Sep 14 '16 at 18:53
@AlexParedes due to the fact that you post this question to the SQL Server topic first, I suppose that maybe you use a really old version of SQL Server, that is why I propose this solution. As for SQLite, looks like the query can a bit simpler: CAST('67.896' AS int). I will update the answer accordingly. – Denis Reznik Sep 14 '16 at 19:00

DatabaseCoder · Answer 2 · 2016-09-14T19:32:22.460

-2

--declaring variable table for example
declare @x table (value real)

--inserting some dummy values in variable table
insert into @x (value)
select '12.45'
union
select '56.789'

select * from @x

--updating variable table values using floor function
--floor function Returns the largest integer less than or equal to the specified numeric expression
update @x set value = floor(value)

select * from @x

edited Sep 14 '16 at 19:32

answered Sep 14 '16 at 18:35

DatabaseCoder

2,004
2
12
22

Thank you for answering but I understood nothing... – Alex Paredes Sep 14 '16 at 18:40

Set floor/round value sql value

2 Answers2