Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Dave Windsor
Starting Member
2 Posts |
Posted - 2013-06-27 : 19:12:33
|
I have a money field that I have appended the $ sign to it and added the commas every three digits to the left of the decimal. However I want to also remove the last two decimal places as they are redundant becasue the UI input does not allow any decimal entry. Here is the expression:'$'+CONVERT(char,Value,1) [Project Value]Where "Value" is name of the money field and I get this as a result $5,000,000.00 and I want to truncate the decimal places. Can you please help? |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-27 : 19:54:40
|
[CODE]'$'+REVERSE(STUFF(REVERSE(CAST(Value as VARCHAR)), 1, 3, '')) [Project Value][/CODE] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-28 : 01:05:41
|
why do you need to do this in sql? looks like a presentation issue to me which can be very easily handled at front end using formatting functions.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Dave Windsor
Starting Member
2 Posts |
Posted - 2013-07-01 : 08:02:13
|
[quote]Originally posted by MuMu88 [CODE]'$'+REVERSE(STUFF(REVERSE(CAST(Value as VARCHAR)), 1, 3, '')) [Project Value]This worked like a champ thank you |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-01 : 10:45:04
|
quote: Originally posted by MuMu88 [CODE]'$'+REVERSE(STUFF(REVERSE(CAST(Value as VARCHAR)), 1, 3, '')) [Project Value][/CODE]
Though not much pronounced in this case, always make sure you specify a length while casting to varcharseehttp://visakhm.blogspot.in/2010/02/importance-of-specifying-length-in.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
chbala85
Starting Member
49 Posts |
Posted - 2013-07-02 : 02:12:25
|
declare @num moneyset @num='$5,000,000.00'print @numSELECT '$'+STUFF(@num, CHARINDEX('.',@NUM,1), 3, '')[Project Value] |
|
|
|
|
|
|
|