Search code examples
mysqlsqlmariadbtruncate

MariaDB SQL query returns truncated numbers from select statement

I am trying to pull data from Maria DB. The datatype in schema is DECIMAL (12, 8). In my program when I query using following query. it trucates to 4 (or 3) decimal places and also round off.

select CAST(FORMAT(latitude, 100) AS FLOAT) latitude from mytable

it returns 36.173 . In DB it is stored as 36.17298200 I want it to return as 36.172982

Solution

  • According to official MySQL documents:

    The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC.

    Floating-Point Types (Approximate Value) - FLOAT, DOUBLE

    So you should be writing something similar to:

    select CAST(FORMAT(latitude, 100) AS DECIMAL(8,6)) latitude from mytable

    As 8 is the total number of digits and 6 is precision.