Remove decimal values

Question

Remove decimal values

vigneshkumart50

Ten Centuries

Points: 1205
More actions
May 2, 2014 at 2:02 am

#306956

Hi,
I am using this query to show my column as money and I need to remove the decimal values on top of this query
Query:
'$ '+ Replace(CONVERT(varchar,CAST(Finance_Report As money),1),'.00','') as FinanceReports,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

vigneshkumart50 (5/2/2014)
Hi,
I am using this query to show my column as money and I need to remove the decimal values on top of this query
Query:
'$ '+ Replace(CONVERT(varchar,CAST(Finance_Report As money),1),'.00','') as FinanceReports,

What data type is column Finance_Report?

Please provide a script to generate a few sample rows with representative values, along with the expected output from those values.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vigneshkumart50 Ten Centuries Points: 1205 More actions · Answer 2

the datatype is decimal(10,4)

Current Value:

12,589.25

Expected Output:

12,589

sql crazy kid Ten Centuries Points: 1388 More actions · Answer 3

I maynot be clear the scenarios . But let ask you why don't you convert into INT instead of Money . Any way you don't need decimal places.

David Burrows SSC Guru Points: 65143 More actions · Answer 4

Normally this is done in the presentation layer, but if you must then

'$' + REVERSE(SUBSTRING(REVERSE(CONVERT(varchar,CAST(Finance_Report As money),1)),4,255)) AS [FinanceReports]

Far away is close at hand in the images of elsewhere.
Anon.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

vigneshkumart50 (5/2/2014)
the datatype is decimal(10,4)
Current Value:
12,589.25
Expected Output:
12,589

Always truncating, never rounding? What about 12,589.99?

Your expected output omits a '$' character present in your first post, do you want this or not?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden