September 8, 2011 at 12:15 am
Hi,
Would like to know how to format a number from this format: 11000 to this format: 11,000 and if the value is negative, the format will appear like this: (11,000)
I had tried the method from this link but it's not working:
http://www.baycongroup.com/learning_sql.htm
Please advise.
September 8, 2011 at 12:24 am
Here is the code, hope this will be helpful
SELECT convert(varchar,convert(Money, colName),1)FROM tableName
Have a nice day!
September 8, 2011 at 12:44 am
I would do this one with the tool that is displaying the data as they ususally have much better format masking. Remember a database is for managing data and a display tool is about showing the data in a user friendly format.
September 8, 2011 at 3:00 am
dogramone (9/8/2011)
I would do this one with the tool that is displaying the data as they ususally have much better format masking. Remember a database is for managing data and a display tool is about showing the data in a user friendly format.
I was also hoping that the reporting tool can cater for the format but the tool seems like got some bugs and I don't have the patience of waiting for the bugs to be fixed... 🙂
September 8, 2011 at 3:33 am
This is a presentation layer task and should be dealt with there.
However, you can do it in t-sql like this: -
DECLARE @values AS TABLE (value INT)
INSERT INTO @values
SELECT 11000
UNION ALL SELECT -11000
SELECT CASE WHEN value < 0
THEN '('+CONVERT(VARCHAR(100),value * (-1))+')'
ELSE LEFT(CONVERT(VARCHAR(100),(CAST(value AS MONEY)),1),CHARINDEX('.',(CONVERT(VARCHAR(100),(CAST(value AS MONEY)),1)))-1) END
FROM @values
That returns the value 11000 as 11,000 and the value -11000 as (11000).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply