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))
How to turn Money to Decimal? e.g. $1M to 1000000 and $2B to 2000000000?
sql server 2008 - TSQL Cast the sum as Money - Stack Overflow
Conversion of currency stored as varchar to decimal – SQLServerCentral Forums
string - Microsoft SQL Server 2016 - Convert a varchar to money with a $ sign & 2 decimals - Stack Overflow
Videos
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))
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
Hello, I'm trying to convert two of my money columns into decimals but I'm failing miserably.
SELECT valuation,
SUBSTR(valuation, 2) AS numeric_part, SAFE_CAST(SUBSTR(valuation, 2) AS FLOAT64) AS float_value, CASE WHEN STRPOS(valuation, 'B') > 0 THEN 1e9 WHEN STRPOS(valuation, 'M') > 0 THEN 1e6 ELSE 1 END AS multiplier FROM `unicorns-405719.unicorn.unival`;
I also used CAST and CASE but all they do is remove the dollar sign without actually multiplying the M by 6 zeroes or the B by 9.
I'm using BigQuery and I keep getting errors and Idk what to do. I'm about to give up and use Excel instead.
This is something that should be done on the presentation layer, but if you need to do this in sql you can use:
'$'+convert(varchar(50), CAST(amount as money), -1) amount
Here is an example:
;with cte (amount)
as
(
select 123254578.00 union all
select 99966.00 union all
select 0.00 union all
select 6275.00 union all
select 18964.00 union all
select 1383.36 union all
select 26622.36
)
select '$'+convert(varchar(50), CAST(amount as money), -1) amount
from cte
See SQL Fiddle with Demo. This returns:
| AMOUNT |
-------------------
| 
99,966.00 |
| 
6,275.00 |
| 
1,383.36 |
| $26,622.36 |
Note: This will be much easier in SQL Server 2012 because you can use FORMAT()
;with cte (amount)
as
(
select 123254578.00 union all
select 99966.00 union all
select 0.00 union all
select 6275.00 union all
select 18964.00 union all
select 1383.36 union all
select 26622.36
)
select '$'+FORMAT(amount,'#,0.0000') amount
from cte
See SQL Fiddle with Demo
Adding to bluefeet's answer, the FORMAT function available in SQL 2012 could be done like this:
SELECT FORMAT(12345.6789, 'C', 'en-us')
The C means currency, and the last argument is culture. The culture is important if you want your application to be multilingual, because it takes care of things like dollar (or euro) signs, and the thousands separator. For instance:
SELECT
FORMAT(12345.6789, 'C', 'en-us') as "USA",
FORMAT(12345.6789, 'C', 'fr-ca') as "French Canada",
FORMAT(12345.6789, 'C', 'fr-fr') as "French France",
FORMAT(12345.6789, 'C', 'hi-in') as "Hindi India"
Will give this result:
USA French Canada French France Hindi India
----------- ------------- -------------- --------------
12 345,68 € ₹ 12,345.68
Hi BobbyP-1695,
You can try to convert money data type to decimal(19,4).
And you can try to add a new column, copy existing data to that column, rename the old column, and change the data type. In case something goes wrong, you can switch the old column back.
Please refer to https://stackoverflow.com/questions/224462/storing-money-in-a-decimal-column-what-precision-and-scale which might be helpful.
Best Regards,
Amelia
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Why do you want to change the datatype from Money to something else?
As long as you are converting it to a compatible data type, you can simply change the data type in place.
See:
https://learn.microsoft.com/en-us/sql/relational-databases/tables/modify-columns-database-engine?view=sql-server-ver15