Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remove decimal values Expand / Collapse
Author
Message
Posted Friday, May 2, 2014 2:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:26 AM
Points: 48, Visits: 147
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,
Post #1566948
Posted Friday, May 2, 2014 2:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 7,191, Visits: 13,645
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1566951
Posted Friday, May 2, 2014 2:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:26 AM
Points: 48, Visits: 147
the datatype is decimal(10,4)

Current Value:
12,589.25

Expected Output:
12,589
Post #1566959
Posted Friday, May 2, 2014 3:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 5:14 AM
Points: 48, Visits: 161
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.
Post #1566962
Posted Friday, May 2, 2014 3:07 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 7,089, Visits: 6,898
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.

Post #1566965
Posted Friday, May 2, 2014 3:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 7,191, Visits: 13,645
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1566966
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse