Solved

t-sql money data type decimal places

Posted on 2016-11-08
4
88 Views
Last Modified: 2016-11-16
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
Comment
Question by:maqskywalker
  • 2
4 Comments
 
LVL 52

Expert Comment

by:_agx_
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)

Open in new window

0
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 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

by:
_agx_ earned 250 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

by:maqskywalker
ID: 41889712
thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question