3

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?

Robbert
  • 6,481
  • 5
  • 35
  • 61
  • Why float vs double? http://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html or http://stackoverflow.com/questions/19601975/storing-statistical-data-do-i-need-decimal-float-or-double – xQbert Aug 06 '14 at 16:18
  • No reason other than I don't need double precision. – Robbert Aug 06 '14 at 16:20
  • 2
    Simple guess: try moving all the `declare` statements to the beginning of the function/procedure, and any other instruction (including the `set` statements) after the `declare`s. Read my answer below – Barranka Aug 06 '14 at 16:20
  • http://stackoverflow.com/questions/11719044/how-to-get-a-float-result-by-dividing-two-integer-values – Ciro Santilli OurBigBook.com Sep 04 '15 at 10:01

2 Answers2

5

Can you try this?

SELECT CAST(time AS decimal) / CAST(100 AS decimal)
Areza
  • 5,623
  • 7
  • 48
  • 79
user1874538
  • 262
  • 2
  • 15
  • Same error. `DECLARE dtime DECIMAL; SET dtime = cast(time as decimal) / cast(100 as decimal);` Error `near 'DECLARE ftime VARCHAR(10);` – Robbert Aug 06 '14 at 16:22
  • See the answer above. I had assumed declare was at the start of the snip it. – user1874538 Aug 06 '14 at 16:26
2

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.

Community
  • 1
  • 1
Barranka
  • 20,547
  • 13
  • 65
  • 83