Menu
I am using Oracle SQL database and I have to insert a monetary value(salary) as part of a row. For some strange reason the money command isnt working, is there any alternates that would work with this?
Data input format: £00,000.000
CREATE TABLE staff
(staffno CHAR(6) NOT NULL
, staffsurname VARCHAR(8) NOT NULL
, staffforename VARCHAR(7) NOT NULL
, salary MONEY NOT NULL
, PRIMARY KEY (staffno)
);
283
The money data type is an abstract data type. Money values are stored significant to two decimal places. These values are rounded to their amounts in dollars and cents or other currency units on input and output, and arithmetic operations on the money data type retain two-decimal-place precision.
The best type for price column should be DECIMAL. The type DECIMAL stores the value precisely. For Example - DECIMAL(10,2) can be used to store price value. It means the total digit will be 10 and two digits will be after decimal point.
If you need the highest precision, a DECIMAL can use up to 17 bytes for each value. Generally though, I like using DECIMAL(19,4) for currency, which needs 9 bytes and can store numbers 19 digits wide, where the last four digits are after the decimal place.
Look at this line
salary MONEY NOT NULL
There is no existing money datatype.
If you are looking for something similar to SQL-Server small money type, you want to use a Number(10,4) and then format the number.
You can format the number using to_char function
select to_char(yourColumn, '$99,999.99') from yourTable where someCondition
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
