March 7, 2013 at 11:21 pm
Hi,
I heard we can insert currency Symbols like $ to Money and small money datatypes. But while i am trying i realized we can insert $ into money datatype but it will not display symbol while viewing table content. Am i missing anything or it is like that only? If so What is need of money datatype because we can use decimal or nvarchar only?
Thank you
March 7, 2013 at 11:34 pm
winmansoft (3/7/2013)
Hi,I heard we can insert currency Symbols like $ to Money and small money datatypes. But while i am trying i realized we can insert $ into money datatype but it will not display symbol while viewing table content. Am i missing anything or it is like that only? If so What is need of money datatype because we can use decimal or nvarchar only?
Thank you
You can't .. that's how SQL Server stores it .. check it out on this link
http://msdn.microsoft.com/en-us/library/ms179882.aspx
about the money datatype .. I haven't used it ..
but it's definitely better that varchar .. where you keep symbol ..and then you need to aggregate it ...
So, the process would be >> replace>> convert >> aggregate..
as business point of view ,when you got money in the table , it is used for all kind of arithmetics ..
that's what I could think of...will wait for someone to give a techincal point of view on this ..
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
March 8, 2013 at 12:30 am
demonfox (3/7/2013)
You can't .. that's how SQL Server stores it .. check it out on this link
http://msdn.microsoft.com/en-us/library/ms179882.aspx
..
I found this in your link
It is important to remember that while you can specify monetary values preceded by a currency symbol, SQL Server does not store any currency information associated with the symbol, it only stores the numeric value.
what that means SQL Server does not store any currency information??then what is the use of inserting currency symbol?? And your link has list of symbols. How can i type symbols other than $ in query?
March 8, 2013 at 1:36 am
winmansoft (3/8/2013)
demonfox (3/7/2013)
You can't .. that's how SQL Server stores it .. check it out on this link
http://msdn.microsoft.com/en-us/library/ms179882.aspx
..
I found this in your link
It is important to remember that while you can specify monetary values preceded by a currency symbol, SQL Server does not store any currency information associated with the symbol, it only stores the numeric value.
what that means SQL Server does not store any currency information??then what is the use of inserting currency symbol?? And your link has list of symbols. How can i type symbols other than $ in query?
okay, If your requirement is to have a column where in currency symbols has to be type in then go with varchar ..
or you can have a Currency Code column where in you store the information of currency types ...
how is the data coming into the table ??
do you have control over tables architecture and design ? why are you planning on inserting the currency with symbols ..
can you post the DDL's and the reason how you came across this ???
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
March 8, 2013 at 1:51 am
Another thought as well for you - which do you think is the least impact on your server when running a couple of million rows
SELECT
col1 *1.02679
FROM tablea
or
SELECT
CONVERT(MONEY,REPLACE (col1,'$',''))*1.02679
FROM tablea
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
March 11, 2013 at 7:12 pm
Hi Winmasoft,
I had similar questions to these:
"then what is the use of inserting currency symbol?? And your link has list of symbols. How can i type symbols other than $ in query?"
The only answer that makes sense so far is that it vallows the insertion of values that include the symbol without stripping it off. I set up a test table and succesfully ran the following INSERT statements:
INSERT INTO TheMoneyTable (TheMoney) VALUES (£123.5678);
INSERT INTO TheMoneyTable (TheMoney) VALUES ($987.6543);
INSERT INTO TheMoneyTable (TheMoney) VALUES (¥3512.8956);
Not sure if this would be useful to you, but I used MS Word to paste the currency symbols into the SSMS query window. The actual unicode vbalues can be retrieved as follows:
/* Returns 163 */
SELECT UNICODE('£');
/* Returns £ */
SELECT NCHAR(163);
Hope it helps - if there is another reason for supporting the currency symbol functuionality, I'd be curious to know also! 🙂
March 11, 2013 at 11:30 pm
jformosa (3/11/2013)
Hi Winmasoft,I had similar questions to these:
"then what is the use of inserting currency symbol?? And your link has list of symbols. How can i type symbols other than $ in query?"
The only answer that makes sense so far is that it vallows the insertion of values that include the symbol without stripping it off. I set up a test table and succesfully ran the following INSERT statements:
INSERT INTO TheMoneyTable (TheMoney) VALUES (£123.5678);
INSERT INTO TheMoneyTable (TheMoney) VALUES ($987.6543);
INSERT INTO TheMoneyTable (TheMoney) VALUES (¥3512.8956);
Not sure if this would be useful to you, but I used MS Word to paste the currency symbols into the SSMS query window. The actual unicode vbalues can be retrieved as follows:
/* Returns 163 */
SELECT UNICODE('£');
/* Returns £ */
SELECT NCHAR(163);
Hope it helps - if there is another reason for supporting the currency symbol functuionality, I'd be curious to know also! 🙂
Ya me too tested this... But inserted currency symbols will not be visible if we do 'select' and in msdn page it clearly says that it will not store any currency information. Then i am thinking what is the use of inserting currency symbol in money datatype?what is the use of money datatype?
March 11, 2013 at 11:38 pm
demonfox (3/8/2013)
okay, If your requirement is to have a column where in currency symbols has to be type in then go with varchar ..or you can have a Currency Code column where in you store the information of currency types ...
how is the data coming into the table ??
do you have control over tables architecture and design ? why are you planning on inserting the currency with symbols ..
can you post the DDL's and the reason how you came across this ???
Somewhere it said that we can insert currency symbols in money datatype.so i got curious. I thought we can process money datatype with currency symbols. I just want to know what is the use of money datatype? In your link they have given list of currency symbols and clearly i don't know how to type those symbols in SSMS? It says money will not store currency information associated with symbol and it definitely not storing currency symbol. Then why we want to insert currency symbol?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply