Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# t-sql money data type decimal places

Posted on 2016-11-08
Medium Priority
410 Views
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
Question by:maqskywalker
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 52

Expert Comment

ID: 41879387
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)
``````
0

LVL 30

Assisted Solution

Pawan Kumar earned 1000 total points
ID: 41879801
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

LVL 52

Accepted Solution

_agx_ earned 1000 total points
ID: 41879833
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

LVL 1

Author Closing Comment

ID: 41889712
thanks.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
###### Suggested Courses
Course of the Month6 days, 7 hours left to enroll