CODEWITHSUNDEEP
mysqlfloating-pointuser-defined-functionsinteger-division
Robbert
Robbert

Reputation: 6592

Divide two integers to get a float in MySQL

I'm working on a user defined function in MySQL that takes an integer (mediumint) and divides it by 100 (i.e. 10785 / 100 = 107.85). The supplied integer is a parameter called time. I've tried

DECLARE dtime FLOAT;
SET dtime = time / 100;
DECLARE ftime VARCHAR(10);

which is causing an error, I assume because I'm dividing two integers and assigning it to a float

MySQL said: #1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 
'DECLARE ftime VARCHAR(10);

I've also tried 

SET dtime = CAST(time as FLOAT) / 100.0;

But that throws an error because it appears you can't cast a number to a float. Finally, I tried using decimal

DECLARE dtime DECIMAL(10,10);
SET dtime = CAST(time AS decimal(10,10)) / CAST(100 as decimal(10,10);
DECLARE ftime VARCHAR(10);

But throws the same error (near 'Declare ftime...). What is the proper way to accomplish this?

Upvotes: 4

Views: 11146

Answers (2)

user1874538
user1874538

Reputation: 262

Can you try this?

SELECT CAST(time AS decimal) / CAST(100 AS decimal)

Upvotes: 5

Barranka
Barranka

Reputation: 21047

Please read the documentation for the DECLARE statement syntax.

Quoting from the reference manual (the above link):

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

Declarations must follow a certain order. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations.

Upvotes: 2

Related Questions