How do I convert from a money datatype in SQL server

Question

I have a money data type in SQL Server  How do I reformat 0 00 to 0 in my query

User · Answer

I had this issue as well  and was tripped up for a while on it   I wanted to display 0 00 as 0 and otherwise keep the decimal point   The following didn t work   CASE WHEN Amount  0 THEN CONVERT VARCHAR 30   Amount  1  ELSE Amount END   Because the resulting column was forced to be a MONEY column   To resolve it  the following worked  CASE WHEN Amount  0 THEN CONVERT VARCHAR 30    0   1  ELSE CONVERT  VARCHAR 30   Amount  1  END   This mattered because my final destination column was a VARCHAR 30   and the consumers of that column would error out if an amount was  0 00  instead of  0

User · Answer

This looks like a formating issue to me  As far as SQL Server s money type is concerned 0    0 00  If you re trying to display 0 in say c  rather then 0 00 you should convert it to a string  and format it as you want   or truncate it

User · Answer

Normal money conversions will preserve individual pennies   SELECT convert varchar 30   moneyfield  1    The last parameter decides what the output format looks like   0  default  No commas every three digits to the left of the decimal point  and two digits to the right of the decimal point  for example  4235 98    1 Commas every three digits to the left of the decimal point  and two digits to the right of the decimal point  for example  3 510 92    2 No commas every three digits to the left of the decimal point  and four digits to the right of the decimal point  for example  4235 9819     If you want to truncate the pennies  and count in pounds  you can use rounding to the nearest pound  floor to the lowest whole pound  or ceiling to round up the pounds   SELECT convert int  round moneyfield  0   SELECT convert int  floor moneyfield   SELECT convert int  ceiling moneyfield

User · Answer

Would casting it to int help you   Money is meant to have the decimal places     DECLARE  test AS money SET  test   3 SELECT CAST  test AS int    test

User · Answer

It seems despite the intrinsic limitations of the money datatype  if you re already using it  or have inherited it as I have  the answer to your question is  use DECIMAL

User · Answer

you could either use   SELECT PARSENAME      Convert varchar Convert money  MoneyValue  1  2    or   SELECT CurrencyNoDecimals        LEFT  CONVERT varchar   MoneyValue 1              LEN  CONVERT varchar   MoneyValue 1   - 2

User · Answer

You can try like this   SELECT PARSENAME      Convert varchar Convert money  MoneyValue  1  2

User · Answer

I found this approach direct and useful   CONVERT VARCHAR 10   CONVERT MONEY  fieldname   AS PRICE

User · Answer

First of all  you should never use the money datatype  If you do any calculations you will get truncated results  Run the following to see what I mean  DECLARE      mon1 MONEY       mon2 MONEY       mon3 MONEY       mon4 MONEY       num1 DECIMAL 19 4        num2 DECIMAL 19 4        num3 DECIMAL 19 4        num4 DECIMAL 19 4       SELECT      mon1   100   mon2   339   mon3   10000       num1   100   num2   339   num3   10000      SET  mon4    mon1  mon2  mon3     SET  num4    num1  num2  num3      SELECT  mon4 AS moneyresult       num4 AS numericresult   Output  2949 0000    2949 8525  Now to answer your question  it was a little vague   the money datatype always has two places after the decimal point  Use the integer datatype if you don t want the fractional part or convert to int    Perhaps you want to use the decimal or numeric datatype

[sql-server] How do I convert from a money datatype in SQL server?

Examples related to sql-server

Examples related to currency