Solved

Displaying Currency without the Decimal in SQL

Posted on 2012-03-13
5
407 Views
Last Modified: 2012-03-13
I have a Column in a Table that has Currency input into it.  I need to display this Currency as part of a String without the Decimal.

How do I go about doing this?

This is what I have thus far
SELECT '0000012345678901'+LTRIM(CAST(ITEMNO AS VARCHAR(20)))+''+CAST(checknet AS VARCHAR(20))+''+CONVERT(varchar(10),checkdt, 112)+CASE(ckstatus)
WHEN 'O' THEN '' WHEN 'V' THEN 'C' END +''  as Val
FROM mytable

Where you see +CAST(checknet AS VARCHAR(20)) is where I need to extract the information from that Column and display it without the Decimal.

Thank you
0
Comment
Question by:Rmumpower00
  • 2
  • 2
5 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 37717228
Hi Rmum,

Probably the easiest way is just to cast it to an integer.

  SELECT cast (column * 100 as INTEGER) FROM ...

That will preserve the two decimal places and display the entire value.



Good Luck,
Kent
0
 

Author Comment

by:Rmumpower00
ID: 37717262
Kent,
Could you possibly put what you're talking about into the string that I listed?
0
 
LVL 2

Accepted Solution

by:
musickmann earned 500 total points
ID: 37717278
Would using the REPLACE (string_expression, search_string,  replacement_string) function work for you? Might look something like this:


 REPLACE (CAST(checknet AS VARCHAR(20)), '.',  '')
0
 

Author Comment

by:Rmumpower00
ID: 37717288
Musickmann That worked!!!!!!!!!!

Thank you!
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37717299
SELECT
  '0000012345678901'+LTRIM(CAST(ITEMNO AS VARCHAR(20)))+''+
  cast (cast (column * 100 as INTEGER) as varchar(20) +
  ''+CONVERT(varchar(10),checkdt, 112)+
  CASE(ckstatus) WHEN 'O' THEN '' WHEN 'V' THEN 'C' END +''  as Val
FROM mytable

Though the double casting might make musickmann's suggestion to use REPLACE easier.


Kent
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Join vs where 2 38
Insert Salary Period that has 2 months 11 22
TSQL DateADD update Question 4 29
sql Total query 2 16
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now