In SQL, the data type used to store currency values is commonly referred to as Currency or Money. This data type is specifically designed to handle monetary values and calculations accurately and efficiently.
The Currency Data Type
The currency data type allows you to store fixed-point decimal numbers with precision and scale. The precision defines the total number of digits that can be stored, while the scale determines the number of decimal places. For example, a column defined as Currency(10, 2) can hold values up to 10 digits with 2 decimal places.
Note: The actual storage size of the currency data type depends on the database system you are using. It typically ranges from 4 to 8 bytes.
Working with Currency Data
The currency data type in SQL provides various built-in functions and operators for performing arithmetic operations on currency values. These include addition, subtraction, multiplication, division, as well as comparison operations like greater than, less than, equal to, etc.
- Create a table named Products with a column named Price of type Currency.
- Insert some sample data into the table.
- Select all products where the price is greater than $50.
CREATE TABLE Products ( ProductID int, Name varchar(50), Price money ); INSERT INTO Products (ProductID, Name, Price) VALUES (1, 'Product A', 49.99), (2, 'Product B', 75.50), (3, 'Product C', 99.99); SELECT * FROM Products WHERE Price > 50;
In the above example, the Price column is of type Money, allowing us to store and compare currency values easily.
Formatting Currency Values
When retrieving currency values from a database, it is often necessary to format them in a human-readable way. SQL provides various formatting functions to achieve this.
- Select the product name and price, formatted as currency.
SELECT Name, FORMAT(Price, 'C') AS FormattedPrice FROM Products;
The FORMAT() function is used to format the Price column as currency using the ‘C’ format specifier. This will display the currency symbol and appropriate decimal places based on the system’s regional settings.
The currency data type in SQL provides a reliable and efficient way to store and manipulate monetary values. With its precision and scale capabilities, along with built-in functions for arithmetic operations and formatting, it simplifies working with financial data.
Remember to choose the appropriate precision and scale when defining a currency column based on your specific requirements. Additionally, consider regional settings when formatting currency values for display.
Note: It’s important to consult your database documentation or refer to specific database system guidelines for exact syntax and behavior related to the currency data type as it may vary across different systems.