Format a number with commas but without decimals in SQL Server 2008 R2?

Question

I am using the following to create a comma formatted number in T-SQL. How can I get rid of the decimal point and the digits after decimal. So if I get 1,112.00 after formatting, how would I get only 1,112?

SELECT CONVERT(varchar, CAST(1112 AS money), 1)

Answer 1

A great funcion. use second para ###,###:

create function dbo.formateoingles(@CampoFormat sql_variant, @Formato as varchar(20)=NULL)
 RETURNS VarChar(8000)
 -- Formatos Soportados
         -- Fecha
         --('d', 'dd', 'dddd', 'm', 'mm', 'mmm', 'mmmm' , 'yy', 'yyyy'
         -- 'd mm', 'd-mm', 'd/mm', 'd.mm', 'dd mm', 'dd-mm', 'dd/mm', 'dd.mm', 'ddd mm', 'ddd-mm', 'ddd/mm', 'ddd.mm'
         -- 'dddd mm', 'dddd-mm', 'dddd/mm', 'dddd.mm', 'dd mmm', 'dd-mmm', 'dd/mmm', 'dd.mmm', 'dd mmmm' 'dd-mmmm', 'dd/mmmm', 'dd.mmmm'
         -- 'm yy', 'm-yy', 'm/yy', 'm.yy', 'mm yy', 'mm-yy', 'mm/yy', 'mm.yy',
         -- 'mmm yy', 'mmm-yy', 'mmm/yy', 'mmm.yy', 'mmmm yy',
         -- 'mmmm-yy', 'mmmm/yy', 'mmmm.yy', 'mmmm yy', 'mmmm-yy', 'mmmm/yy', 'mmmm.yy',
         -- 'mmm yyyy', 'mmm-yyyy', 'mmm/yyyy', 'mmm.yyyy',
         -- 'mmmm yyyy', 'mmmm-yyyy' 'mmmm/yyyy' 'mmmm.yyyy' ,
         -- 'd m yy', 'd-m-yy', 'd/m/yy', 'd.m.yy', 'd mm yy', 'd-mm-yy',
         -- 'd/mm/yy', 'd.mm.yy' ,  'dd mm yy' ,'dd-mm-yy' ,'dd/mm/yy' ,'dd.mm.yy',
         -- 'd m yyyy' ,'d-m-yyyy' ,'d/m/yyyy' ,'d.m.yyyy'  , 'd mm yyyy' ,
         -- 'd-mm-yyyy' ,'d/mm/yyyy' ,'d.mm.yyyy' ,  'dd mm yyyy' ,'dd-mm-yyyy' ,
         -- 'dd/mm/yyyy' ,'dd.mm.yyyy','ddd mmm y' ,'ddd-mmm-y' ,'dddd/mmm/y' ,'ddd.mmm.y' ,'ddd mmm yy' ,
         -- 'ddd-mmm-yy' ,'ddd/mmm/yy' ,'ddd.mmm.yy' ,'ddd mmm yyyy', 'ddd-mmm-yyyy' ,
         -- 'dddd/mmm/yyyy' ,'dddd.mmmm.yyyy' ,'dddd mmmm yyyy' ,'dddd-mmmm-yyyy' ,'dddd/mmmm/yyyy' ,'dddd.mmmm.yyyy'
         -- Numeros
         --('###,###.#0', '###,###')
 --
 -- Funcion Creada por Jimmy Poma e-mail jdak67@hotmail.com  Fecha 18/agos/2004
 -- Modificado Por Jimmy Fecha 19/Agosto/2004 ora 13:25 para que acepte El Formato Null y devuelva tal como lo envio
 -- Modificado Por Jimmy Fecha 20/Agosto/2004 ora 13:25 para que acepte todos los Formatos Fecha o almenos casi todos :)
 -- Todos los derechos reservados :) no, menrita jeje
  -- SI LE REALIZAN ALGUN CAMBIO ME AVISAS PARA TENERLO ACTUALIZADO AL jdak67@hotmail.com
 ---***************************************************************************************************************************************************************
 as
 BEGIN
         --Declaracion de Variables
         Declare @Trim SQL_VARIANT
         Declare @PosIni as int
         Declare @CantPos as int
 
         Declare @CantDecimales  as int
 
         Declare @TipoFecha as int
         Declare @Devuelve as varchar(8000)
         Declare @TipoFormato as int
         Declare @Separador as nVarChar(1)
 
         Declare @Dia as varchar(50)
         Declare @Mes as varchar(50)
         Declare @Anio as varchar(50)
         
 
         DECLARE @Primero as bit
         DECLARE @LOOP as bit
 
         --VALIDA EL FORMATO SI ES NUMERICO, FECHA O CUALQUIER OTRO TIPO
         IF @Formato IS NULL
         BEGIN
                 SET @Devuelve = CONVERT(VARCHAR(8000), @CampoFormat)
                 GoTo Salir
         End
         If PATINDEX('%#%',@Formato)<=0
         BEGIN
 
                 If @Formato = 'd'  or @Formato = 'dd'
                 BEGIN
                         --Va a mostrar dia
                         SET @TipoFormato  = 1
                         SET @TipoFecha = 103
                 End
 
                 If @Formato = 'ddd'
                 BEGIN
                         --Va a mostrar Lun
                         SET @TipoFormato  = 2
                         SET @TipoFecha = 103
                 End
 
                 If @Formato = 'dddd'
                 BEGIN
                         --Va a mostrar Lunes
                         SET @TipoFormato  = 3
                         SET @TipoFecha = 103
                 End
 
                 If @Formato = 'm'  or @Formato = 'mm'
                 BEGIN
                         --Va a mostrar 01
                         SET @TipoFormato  = 4
                         SET @TipoFecha = 101
                 End
  
                 If @Formato = 'mmm'
                 BEGIN
                         --Va a mostrar Ene
                         SET @TipoFormato  = 5
                         SET @TipoFecha = 103
                 End
 
                 If @Formato = 'mmmm'
                 BEGIN
                         --Va a mostrar Enero
                         SET @TipoFormato  = 6
                         SET @TipoFecha = 103
                 End
 
                 If @Formato = 'y'  or @Formato = 'yy'
                 BEGIN
                         --Va a mostrar 79
                         SET @TipoFormato  = 7
                         SET @TipoFecha = 2
                 End
  
                 If @Formato = 'yyy'  or @Formato = 'yyyy'
                 BEGIN
                         --Va a mostrar 1979
                         SET @TipoFormato  = 8
                         SET @TipoFecha = 102
                 End
  
 
                 --PARA LOS DIAS
 
                 If @Formato = 'd mm'  or @Formato = 'd-mm'  or @Formato = 'd/mm'  or @Formato = 'd.mm'  or
                    @Formato = 'dd mm' or  @Formato = 'dd-mm'  or @Formato = 'dd/mm' or @Formato = 'dd.mm'
                 BEGIN
                         --Va a mostrar 01-01
                         SET @TipoFormato  = 9
                         SET @TipoFecha = 103
                 End
  
                 If @Formato = 'ddd mm' or  @Formato = 'ddd-mm'  or @Formato = 'ddd/mm' or @Formato = 'ddd.mm'
                 BEGIN
                         --Va a mostrar en dias Jue 02
                         SET @TipoFormato  = 10
                         SET @TipoFecha = 103
                 End
 
                 If @Formato = 'dddd mm' or  @Formato = 'dddd-mm'  or @Formato = 'dddd/mm' or @Formato = 'dddd.mm'
                 BEGIN
                         --Va a mostrar en dias Jueves 02
                         SET @TipoFormato  = 11
                         SET @TipoFecha = 103
                 End
  
                 --PARA LOS MESES
                 If @Formato = 'dd mmm' or  @Formato = 'dd-mmm'  or @Formato = 'dd/mmm' or @Formato = 'dd.mmm'
                 BEGIN
                         --Va a mostrar en dias 01 Dic
                         SET @TipoFormato  = 12
                         SET @TipoFecha = 103
                 End
 
 
                 If @Formato = 'dd mmmm' or  @Formato = 'dd-mmmm'  or @Formato = 'dd/mmmm' or @Formato = 'dd.mmmm'
                 BEGIN
                         --Va a mostrar en dias 01 Diciembre
                         SET @TipoFormato  = 13
                         SET @TipoFecha = 103
                 End
 
 
                 If @Formato = 'm yy' or  @Formato = 'm-yy'  or @Formato = 'm/yy' or @Formato = 'm.yy' or
                    @Formato = 'mm yy' or  @Formato = 'mm-yy'  or @Formato = 'mm/yy' or @Formato = 'mm.yy'
                 BEGIN
                         --Va a mostrar en dias 01 79
                         SET @TipoFormato  =14
                         SET @TipoFecha = 3
                 End
 
 
                 If @Formato = 'mmm yy' or  @Formato = 'mmm-yy'  or @Formato = 'mmm/yy' or @Formato = 'mmm.yy'
                 BEGIN
                         --Va a mostrar en  Dic 02
                         SET @TipoFormato  =15
                         SET @TipoFecha = 3
                 End
  
                 If @Formato = 'mmmm yy' or  @Formato = 'mmmm-yy'  or @Formato = 'mmmm/yy' or @Formato = 'mmmm.yy'
                 BEGIN
                         --Va a mostrar en  Diciembre 02
                         SET @TipoFormato  =16
                         SET @TipoFecha = 3
                 End
                    
 
                 If @Formato = 'mmm yyyy' or @Formato = 'mmm-yyyy' or @Formato = 'mmm/yyyy' or @Formato = 'mmm.yyyy'
                 BEGIN
                         --Va a mostrar en  Dic 2002
                         SET @TipoFormato  = 17
                         SET @TipoFecha = 103
                 End
 
                 If @Formato = 'mmmm yyyy' or  @Formato = 'mmmm-yyyy'  or @Formato = 'mmmm/yyyy' or @Formato = 'mmmm.yyyy'
                 BEGIN
                         --Va a mostrar en  Diciembre 2002
                         SET @TipoFormato  = 18
                         SET @TipoFecha = 103
                 End
 
                 If @Formato = 'd m yy' or @Formato = 'd-m-yy' or @Formato = 'd/m/yy' or @Formato = 'd.m.yy'   or
                    @Formato = 'd mm yy' or @Formato = 'd-mm-yy' or @Formato = 'd/mm/yy' or @Formato = 'd.mm.yy'   or
                    @Formato = 'dd mm yy' or @Formato = 'dd-mm-yy' or @Formato = 'dd/mm/yy' or @Formato = 'dd.mm.yy'
                 BEGIN
                         --Va a mostrar en  01 02 79
                         SET @TipoFormato = 19
                         SET @TipoFecha = 3              --es el punto
                 End
  
 
                 If @Formato = 'd m yyyy' or @Formato = 'd-m-yyyy' or @Formato = 'd/m/yyyy' or @Formato = 'd.m.yyyy'   or
                    @Formato = 'd mm yyyy' or @Formato = 'd-mm-yyyy' or @Formato = 'd/mm/yyyy' or @Formato = 'd.mm.yyyy'   or
                    @Formato = 'dd mm yyyy' or @Formato = 'dd-mm-yyyy' or @Formato = 'dd/mm/yyyy' or @Formato = 'dd.mm.yyyy'
                 BEGIN
                         --Va a mostrar en  01 02 1979
                         SET @TipoFormato = 20
                         SET @TipoFecha = 103            --es el punto
                 End
 
                 If @Formato = 'ddd mmm y' or @Formato = 'ddd-mmm-y' or @Formato = 'dddd/mmm/y' or @Formato = 'ddd.mmm.y' or
                    @Formato = 'ddd mmm yy' or @Formato = 'ddd-mmm-yy' or @Formato = 'ddd/mmm/yy' or @Formato = 'ddd.mmm.yy'
                 BEGIN
                         --Va a mostrar en  lun Dic 79
                         SET @TipoFormato = 21
                         SET @TipoFecha = 3              --es el punto
                 End
  
                 If @Formato = 'ddd mmm yyyy' or @Formato = 'ddd-mmm-yyyy' or @Formato = 'dddd/mmm/yyyy' or @Formato = 'dddd.mmmm.yyyy'
                 BEGIN
                         --Va a mostrar en  lun Dic 1979
                         SET @TipoFormato = 22
                         SET @TipoFecha = 103            --es el punto
                 End
 
                 If @Formato = 'dddd mmmm yyyy' or @Formato = 'dddd-mmmm-yyyy' or @Formato = 'dddd/mmmm/yyyy' or @Formato = 'dddd.mmmm.yyyy'
                 BEGIN
                         --Va a mostrar en  lunes Diciembre 1979
                         SET @TipoFormato = 23
                         SET @TipoFecha = 103            --es el punto
                 End
                 
                 --ESTA PARTE FALTA
                 If @Formato = 'ddd, dd mmm yyyy' or @Formato = 'ddd-mmm-yyyy' or @Formato = 'dddd/mmm/yyyy' or @Formato = 'dddd.mmmm.yyyy'
                 BEGIN
                         --Va a mostrar en  lun Dic 1979
                         SET @TipoFormato = 24
                         SET @TipoFecha = 103            --es el punto
                 End
 
                 If @Formato = 'dddd mmmm yyyy' or @Formato = 'dddd-mmmm-yyyy' or @Formato = 'dddd/mmmm/yyyy' or @Formato = 'dddd.mmmm.yyyy'
                 BEGIN
                         --Va a mostrar en  lunes Diciembre 1979
                         SET @TipoFormato = 25
                         SET @TipoFecha = 103            --es el punto
                 End
 
 
                 set  @Devuelve  = CASE @TipoFormato
                                                                WHEN 1 THEN SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),1,2)
                                                                WHEN 2 THEN SUBSTRING(DATENAME(dw,CONVERT(VARCHAR(20),@CampoFormat)),1,3)
                                                                WHEN 3 THEN DATENAME(dw,CONVERT(VARCHAR(20),@CampoFormat))
                                                                WHEN 4 THEN SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),1,2)
                                                                WHEN 5 THEN SUBSTRING(DATENAME(mm,CONVERT(VARCHAR(20),@CampoFormat)),1,3)
                                                                WHEN 6 THEN DATENAME(mm,CONVERT(VARCHAR(20),@CampoFormat))
                                                                WHEN 7 THEN SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),1,2)
                                                                WHEN 8 THEN SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),1,4)
                                                                WHEN 9 THEN SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),1,2) + SUBSTRING(@Formato,PATINDEX('%m%',@Formato)-1,1)  + SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),4,2)
                                                                WHEN 10 THEN SUBSTRING(DATENAME(dw,CONVERT(VARCHAR(20),@CampoFormat)),1,3) + SUBSTRING(@Formato,4,1)   + SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),4,2)
                                                                WHEN 11 THEN DATENAME(dw,CONVERT(VARCHAR(20),@CampoFormat)) + SUBSTRING(@Formato,5,1)   + SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),4,2)
                                                                WHEN 12 THEN SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),1,2)  + SUBSTRING(@Formato,3,1)   + SUBSTRING(DATENAME(mm,CONVERT(VARCHAR(20),@CampoFormat)),1,3)
                                                                WHEN 13 THEN SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),1,2)  + SUBSTRING(@Formato,3,1)   + DATENAME(mm,CONVERT(VARCHAR(20),@CampoFormat))
                                                                WHEN 14 THEN SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),4,2)  + SUBSTRING(@Formato,PATINDEX('%y%',@Formato)-1,1)   + SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),7,2)
                                                                WHEN 15 THEN SUBSTRING(DATENAME(mm,CONVERT(VARCHAR(20),@CampoFormat)),1,3)  + SUBSTRING(@Formato,4,1)   + SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),7,2)
                                                                WHEN 16 THEN DATENAME(mm,CONVERT(VARCHAR(20),@CampoFormat))  + SUBSTRING(@Formato,5,1)   + SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),7,2)
                                                                WHEN 17 THEN SUBSTRING(DATENAME(mm,CONVERT(VARCHAR(20),@CampoFormat)),1,3)  + SUBSTRING(@Formato,4,1)   + SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),7,4)
                                                                WHEN 18 THEN DATENAME(mm,CONVERT(VARCHAR(20),@CampoFormat))  + SUBSTRING(@Formato,5,1)   + SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),7,4)
                                                                WHEN 19 THEN SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),1,2)  + SUBSTRING(@Formato,PATINDEX('%m%',@Formato)-1,1)   +  SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),4,2) +  SUBSTRING(@Formato,PATINDEX('%m%',@Formato)-1,1)  + SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),7,2)
                                                                WHEN 20 THEN SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),1,2)  + SUBSTRING(@Formato,PATINDEX('%m%',@Formato)-1,1)   +  SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),4,2) +  SUBSTRING(@Formato,PATINDEX('%m%',@Formato)-1,1)  + SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),7,4)
                                                                WHEN 21 THEN SUBSTRING(DATENAME(dw,CONVERT(VARCHAR(20),@CampoFormat)),1,3) + SUBSTRING(@Formato,PATINDEX('%m%',@Formato)-1,1)   +  SUBSTRING(DATENAME(mm,CONVERT(VARCHAR(20),@CampoFormat)),1,3) +  SUBSTRING(@Formato,PATINDEX('%m%',@Formato)-1,1)  + SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),7,2)
                                                                WHEN 22 THEN SUBSTRING(DATENAME(dw,CONVERT(VARCHAR(20),@CampoFormat)),1,3) + SUBSTRING(@Formato,PATINDEX('%m%',@Formato)-1,1)   +  SUBSTRING(DATENAME(mm,CONVERT(VARCHAR(20),@CampoFormat)),1,3) +  SUBSTRING(@Formato,PATINDEX('%m%',@Formato)-1,1)  + SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),7,4)
                                                                WHEN 23 THEN DATENAME(dw,CONVERT(VARCHAR(20),@CampoFormat)) + SUBSTRING(@Formato,PATINDEX('%m%',@Formato)-1,1)   +  DATENAME(mm,CONVERT(VARCHAR(20),@CampoFormat)) +  SUBSTRING(@Formato,PATINDEX('%m%',@Formato)-1,1)  + SUBSTRING(CONVERT(VARCHAR(10),@CampoFormat,@TipoFecha),7,4)
  
                                                                ELSE CONVERT(VARCHAR(50),@CampoFormat)
                                                                 
  
 
                                                 End
  
                 GoTo Salir
  
 
         End
         Else
         BEGIN
         
                 If @Formato = '###,###.#0'
                 BEGIN
                         SET @CantDecimales = 2
                         SET @CantPos= 15
                         SET @TipoFecha = 101
                         Set @Devuelve = CONVERT( DECIMAL(26,2), @CampoFormat )
                 End
  
                 If @Formato = '###,###'
                 BEGIN
                         SET @CantDecimales = 0
                         SET @PosIni = 1
                         SET @CantPos= 15
                         SET @TipoFecha = 101
                         Set @Devuelve = CONVERT( DECIMAL(26,0), @CampoFormat )
                 End
 
                 set @Primero  = 1
                 SET @LOOP = 1
                 
 
                 WHILE @LOOP=1
                 BEGIN
                         If @Primero = 1
                         BEGIN
                                 SET @Primero = 0
                                 If len(@Devuelve) > 6
                                 BEGIN
                                         if CHARINDEX('.',@Devuelve)> 0
                                         BEGIN
                                                 SET @Devuelve = STUFF(@Devuelve,CHARINDEX('.',@Devuelve)-3, 0, ',')
                                         End
                                         Else
                                         BEGIN
                                                 SET @Devuelve = STUFF(@Devuelve,LEN(@Devuelve)-2, 0, ',')
                                         End
                                 End
                                 Else
                                 BEGIN
                                         SET @LOOP = 0
                                         Break
                                 End
                         End
                         Else
                         BEGIN
                                 IF CHARINDEX(',',@Devuelve) <= 4
                                 BEGIN
                                         SET @LOOP = 0
                                         Break
                                 End
                                 Else
                                 BEGIN
                                         SET @Devuelve = STUFF(@Devuelve,CHARINDEX(',',@Devuelve)-3, 0, ',')
                                 END --IF
                         END     --IF
                         
                 END             --WHILE
                 
 
         End
 Salir:
         Return(@Devuelve)
 
 End
GO

Answer 2

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.

Answer 3

https://database.guide/how-to-format-numbers-in-sql-server/

Starting on SQL 2012 you can write:

SELECT FORMAT(ColumnName, 'N0');

Answer 4

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.

Answer 5

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.

Answer 6

After some research, I found 2 possible answers to my initial question. They are listed below.

Option 1: The answer from Mitch Wheat is a possible answer. However, when one wants to format a column value within a SELECT, then we would have to create a user-defined scalar function using Mitch's T-SQL code, and call this UDF from our SQL.

-- =============================================
-- Description: Formats a number and truncates 
--              the decimal part. You can pass 
--              a number as a string or a numeric type.
-- =============================================
CREATE FUNCTION dbo.Utility_fn_FormatNumberAndTruncateDecimals
(
   @unFormattedNumber VARCHAR(100)
)
 RETURNS VARCHAR(100)
  AS
 BEGIN
   DECLARE @val VARCHAR(100)
   SET @val = convert(VARCHAR(50), cast(@unFormattedNumber AS MONEY), 1)

   RETURN (SELECT left(@val, len(@val) - 3))
 END
 GO

 --call this function using either of the SELECTS below
 SELECT dbo.Utility_fn_FormatNumberAndTruncateDecimals('233444')
 SELECT dbo.Utility_fn_FormatNumberAndTruncateDecimals(233444.345)

Option 2: We can use an inbuilt system function called 'parsename' as in T-SQL code below, to format and truncate decimals.

SELECT PARSENAME(CONVERT(VARCHAR, CAST('2334442221.345222'  AS MONEY), 1),2)