CODEWITHSUNDEEP
sql-server-2005
Ali
Ali

Reputation: 684

SQL Server 2005 money format

I have been using SQL Server 2005, and I need to store some columns in database in Money format.

But SQL Server stores them like this --> 0000.0000, but I only want 2 digits after the decimal point ---> 0000.00

Do I have to control this in the program or there is a way to change this format in SQL Server 2005 ?

Thanks in advance

Upvotes: 0

Views: 6530

Answers (3)

vmvadivel
vmvadivel

Reputation: 1067

I would normally do these formatting stuffs in the front end. But if you still have to do it within the DB layer itself then make use of ROUND function.

SELECT CAST(ROUND(1234.1234, 2) AS MONEY)

Would also suggest you to check out the Performance storage comparison between Money & Decimal post written by Aaron Bertrand: https://sqlblog.org/2008/04/27/performance-storage-comparisons-money-vs-decimal

Money Vs Decimal Vs Float Decision Flowchart (Extract from SQLCAT.com):

Money vs. Decimal vs. Float Decision Flowchart Extract from SQLCat.com Source: http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/09/25/the-many-benefits-of-money-data-type.aspx

Upvotes: 4

Jade
Jade

Reputation: 2992

try this it works for SQL Server 2008 and below (2012 have already a FORMAT() function that you can use)

this will only works for data type Money and SmallMoney

declare @v money -- or smallmoney
set @v = 1000.0123
select convert(varchar(25), @v, 0)
select convert(varchar(25), @v, 1)
select convert(varchar(25), @v, 2)
select convert(varchar(25), @v, 126)

select '$' + convert(varchar(25), @v, 0)
select '$' + convert(varchar(25), @v, 1)
select '$' + convert(varchar(25), @v, 2)
select '$' + convert(varchar(25), @v, 126)

Hope this help!

Upvotes: 2

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

Use Round() function. Returns a numeric value, rounded to the specified length or precision. More here.

Round(value,2,1)

Second parameter specifies precision to be two decimal places.

Third parameter with value one specifies truncate rather than round off.

Upvotes: 2

Related Questions