Concatenate with specific format

Question

I need to concatenate two columns, a year and a month, but still keep a number of characters limited to six. And when the month is less than 10 the output brings the concatenation but only with 5 characters.

The query:

SELECT DISTINCT YEAR(DtFimVigencia) AS Ano,
                MONTH(DtFimVigencia) AS Mês,
                CONVERT(varchar(4), YEAR(DtFimVigencia)) +
                CONVERT(varchar(2), MONTH(DtFimVigencia)) AS AnoMes,
                COUNT(NmContrato) AS Vincendos
FROM Corporativo.VwComercial
WHERE DsRamoWizTOT = 'Residencial'
      AND DtFimVigencia is not null
      AND DsSituacao = 'Emitida'
      AND DsStatus = 'Ativo'
GROUP BY MONTH(DtFimVigencia), YEAR(DtFimVigencia)
ORDER BY YEAR(DtFimVigencia), MONTH(DtFimVigencia) ASC

I needed the format to be 202001, 202002, etc ... Someone knows how to proceed?

Answer 1

Instead of concatenating, you can use the FORMAT. For example:

SELECT ... FORMAT(DtFimVigencia, 'yyyyMM') AS ...