• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1007
  • Last Modified:

t-sql money data type decimal places

I'm using sql server 2008

I have a money column in sql server.
The data in the column has 4 decimal places.

In my select query how to i display only 2 decimal places?
0
maqskywalker
Asked:
maqskywalker
  • 2
2 Solutions
 
_agx_Commented:
Round() or Cast as type decimal, with two decimal places

DECLARE @money money
SET @money = 19.7562            
DECLARE @money money
SET @money = 19.7562            
SELECT  CAST(@money AS DECIMAL(18,2))
		, ROUND(@money, 2)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Try below.. Here you can use Format Function or you can cast to Numeric, in this you can specify how many decimal places you want. I think format is good. <<Format function works only 2012+>>

DECLARE @money MONEY
SET @money = 43875435.324324234

SELECT CAST(@money AS NUMERIC(30,2)) 'UsingCast' , FORMAT(@money, '0.00') 'UsingFormat'

O/p
UsingCast                UsingFormat
43875435.32                43875435.32

You can also use ROUND and Floor functions like below-

SELECT CAST(ROUND(43875435.32932429,2,1) AS DECIMAL (19,2))
SELECT CAST(FLOOR(43875435.32932429*100)/100 AS DECIMAL (19,2))

Hope it helps !!
0
 
_agx_Commented:
SELECT CAST(@money AS NUMERIC(30,2))

That's the same as casting to DECIMAL. Type numeric and decimal are functionally equivalent.


FORMAT(@money, '0.00')

If you need a number, do not use Format. It returns a string, not a number.

>> syntax 1:  ROUND(number,2)  
>> syntax 2:  ROUND(number,2,1)

@maqskywalker - Be aware there's a subtle difference in behavior with ROUND(number, length, [function]).  Syntax 1 rounds the value, so 125.755 would become 125.76.  Syntax 2 truncates it, so 125.755 becomes 125.75
1
 
maqskywalkerAuthor Commented:
thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now