round down a currency string value

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

round down a currency string value

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-20 : 07:26:51

Michael writes "What I am trying to do is round down a value string. E,g 18,185 would become 18,100, 5,692 would become 5,600.

The string is stored as follows 18185.0000, or 5692.0000

Values can have cents (although I just want to show the rounded down dollar amount if greater, generally at the hundreds of dollars position)

I can do this as easy as pie in VB, but my SQL programming is a bit rusty.

I am using Microsoft SQL Server 2000 on Windows XP SP2.

the below statement returns the the string value before rounding

SELECT RIGHT(AmountDescription,2), AmountIncGST AS RoundAmount, FROM VW_FUNDING_SOURCE where ProjectID = '<ProjectID>'"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-05-20 : 07:46:36

Divide your base number down by enough 10's to make get the precision you want, and then look at using the floor or trunc constructs, and then reverse the division by 10's to get back to your original number format.

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-05-20 : 20:10:55

NOTE: works only for rouding down by 100's

DECLARE @yak DECIMAL(9, 4)
SET @yak = 18100.0000
SELECT ROUND (@yak - 50, -2)

Mike Petanovitch

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-21 : 05:05:35

This is what Andrew said:

SELECT FLOOR(10*CAST('18.185' AS MONEY))/10 -- must be . a comma will be considered a thousand separator (maybe depends on regional settings)
UNION ALL
SELECT FLOOR(10*CAST('5.692' AS MONEY))/10

--------------------- 
18.1000
5.6000

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources