# Decimal data type

A synonym for the `decimal`

type is `numeric`

.

A decimal number is characterized by two magnitudes: the *precision*,
which his the total number of decimal digits represented, and the
*scale*, which is the count of digits in the fractional part, to the
right of the decimal point. For example, the number 3.1415 has a
precision of 5 and a scale of 4.

The type `NUMERIC(precision, scale)`

specifies both precision and
scale, both of which must be constants.

The type `NUMERIC(precision)`

is the same as `NUMERIC(precision, 0)`

.

The type `NUMERIC`

is the same as `NUMERIC(MAX_PRECISION, 0)`

.

[!WARNING] This means that casting to

`DECIMAL`

or`NUMERIC`

will round the value to a decimal with no fractional part. Example:`SELECT CAST('0.5' AS DECIMAL)`

will return`1`

as the scale is 0.

The maximum precision supported is 28 decimal digits. The maximum scale supported is 10 decimal digits.

## Operations available for the `decimal`

type

The legal operations are `+`

(plus, unary and binary), `-`

(minus,
unary and binary), `*`

(multiplication), `/`

(division), `%`

(modulus).

Modulus happens as follows:
For: `mod = x % y`

- if
`x >= 0`

and`y > 0`

then:`x - (floor(x / y) * y)`

- if
`x >= 0`

and`y < 0`

then:`x % abs(y)`

- if
`x < 0`

and`y > 0`

then:`- abs(x) % y`

- if
`x < 0`

and`y > 0`

then:`- abs(x) % abs(y)`

Division or modulus by zero cause a runtime error.

Casting a string to a decimal value will produce a run time error if parsing fails.

## Rounding while casting between Decimal types

Rounding is performed using to nearest, ties away from zero strategy.

Example while casting from `DECIMAL(8, 4)`

to `DECIMAL(6, 2)`

:

Input Value | Output Value |
---|---|

1234.1250 | 1234.13 |

-1234.1250 | -1234.13 |

1234.1264 | 1234.13 |

1234.1234 | 1234.12 |

-1234.1264 | -1234.13 |

-1234.1234 | -1234.12 |

### Invalid casts between Decimal types

While casting to decimal types, if the current decimal number cannot be represented with the specified precision and scale, a run time error is thrown.

Example:
Valid casts such as: `CAST('1234.1234' AS DECIMAL(6, 2))`

will return `1234.12`

.
But invalid casts such as: `CAST('1234.1234' AS DECIMAL(6, 3))`

will throw a run time error.

## Predefined functions on Decimal Values

`ROUND(value)` | same as `ROUND(value, 0)` |

`ROUND(value, digits)` | where `digits` is an integer value. Round the value to the specified number of decimal digits after the decimal point. |

`TRUNCATE(value)` | same as `TRUNCATE(value, 0)` |

`TRUNCATE(value, digits)` | where `digits` is an integer value. Truncates the value to the specified number of decimal digits after the decimal point. |

`ABS(value)` | absolute value |

`CEIL(value)` | Ceiling function: nearest integer value greater than or equal to argument (result is a decimal value) |

`FLOOR(value)` | Floor function: nearest integer value less than or equal to argument (result is a decimal value) |

`SIGN(value)` | The "sign" function: -1, 0, or 1 showing if value is <0, =0, or >0. |

`POWER(base, exponent)` | The power function, raising base to the power exponent. |

`SQRT(value)` | Result has type DOUBLE. Square root of value. Produces NaN for negative `value` |

`LN(value)` | The natural logarithm of value. Produces a runtime error for values less than or equal to zero. |

`LOG10(value)` | The logarithm base 10 of value. Produces a runtime error for values less than or equal to zero. |