Format a number with commas but without decimals in SQL Server 2008 R2?
DECLARE @val varchar(50)
set @val = CONVERT(varchar(50), CAST(1112 AS money), 1)
SELECT left(@val, len(@val) - 3)
This also works with digits after the decimal point:
DECLARE @val varchar(50)
set @val = CONVERT(varchar(50), CAST(1112.56 AS money), 1)
SELECT left(@val, len(@val) - 3)
Note: as @Mahmoud Gamal points out, formatting is often more suited to be performed in the front-end.
PARSENAME(CONVERT(VARCHAR,CAST(1112 AS MONEY),1),2)
It will work nicely.
When convert number to money datatype automatically 2 zeros will be added after decimal. PARSENAME function will remove that zeros.
Like so:
SELECT REPLACE(CONVERT(VARCHAR(20), CAST(1112 AS MONEY), 1), '.00', '');
This will always work fine. Since CONVERT(VARCHAR(20), CAST(1112 AS MONEY), 1)
will always return a number with .00
. However, the MitchWheat's answer is better in case there is a number with decimal numbers after the comma.
Note that: You should consider to do this formatting stuff in the front end application. T-SQL is not about formatting.