SQL data type MONEY not outputting $-CodePudding

I'm working on an assignment for my introduction to SQL class and have been having trouble getting certain information to output correctly.

CREATE TABLE PRODUCT(
ProductID       CHAR(5)         NOT NULL,
ProductName     CHAR(20)        NOT NULL,
ProductPrice    MONEY           NOT NULL,
VendorID        CHAR(5)         NOT NULL,
CategoryID      CHAR(5)         NOT NULL,

CONSTRAINT Product_PK PRIMARY KEY (ProductID),

CONSTRAINT Product_FK1 FOREIGN KEY (VendorID)
    REFERENCES VENDOR(VendorID),

CONSTRAINT Product_FK2 FOREIGN KEY (CategoryID)
    REFERENCES CATEGORY(CategoryID)
);
INSERT INTO PRODUCT
VALUES
('1X1', 'Zzz Bag', '$100', 'PG', 'CP'),
('2X2', 'Easy Boot', '$70', 'MK', 'FW'),
('3X3', 'Cosy Sock', '$15', 'MK', 'FW'),
('4X4', 'Dura Boot', '$90', 'PG', 'FW'),
('5X5', 'Tiny Tent', '$150', 'MK', 'CP'),
('6X6', 'Biggy Tent', '$250', 'MK', 'CP')
;

I've written out my code above but when I run the select query:

SELECT *
FROM PRODUCT;

The output I get is:

1X1     Zzz Bag                 100.00  PG      CP   
2X2     Easy Boot               70.00   MK      FW   
3X3     Cosy Sock               15.00   MK      FW   
4X4     Dura Boot               90.00   PG      FW   
5X5     Tiny Tent               150.00  MK      CP   
6X6     Biggy Tent              250.00  MK      CP

and I am hoping to get

1X1     Zzz Bag                 $100.00 PG      CP   
2X2     Easy Boot               $70.00  MK      FW   
3X3     Cosy Sock               $15.00  MK      FW   
4X4     Dura Boot               $90.00  PG      FW   
5X5     Tiny Tent               $150.00 MK      CP   
6X6     Biggy Tent              $250.00 MK      CP

As you can see the $ does not appear for some reason. Any help would be appreciated.

CodePudding user response：

You could do this using format(value, 'C'):

https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver16 https://learn.microsoft.com/en-us/dotnet/standard/base-types/formatting-types

CodePudding user response：

For computers, it is much, much more efficient to store numeric values as binary values rather than as strings of characters. For example, we read “100”, but within the computer it is stored and processed as a value like 000000001100100. Note that for here, “efficient” means “incredibly fast”, and speed = time = money. Especially when you are talking high volume transactions (#/millisecond, #/transaction).

Thus:

When an application reads a value from a database to be displayed to a user, it must format the value in a form that the user can understand. This does not alter the value as stored and processed in the computer’s memory, it just changes how it is displayed on the screen/printed receipt/whatever.
However, to process or store a value, it must be done in a format that is (efficiently!) stored, read, and processed in the database.

The point here is, the database is responsible for storing the value, and only for storing the value. You will need to properly configure your application to get the desired presentation format, with $ (or £ or ¥), along with proper punctuation (1000000 vs. 1,000,000 vs. 1.000.000,00 vs… whatever the users prefer to see).