Filter using only month and year in SQLSERVER

I believe the simplest way is to use the correct format of type date ( here has a list of codes and usable formats for dates):

SELECT NOME_CLIENTE, 
       COUNT(NUMERO_BILHETE) AS 'QUANTIDADE DE BILHETES' 
  FROM BANCO.DBO.CLIENTES
 WHERE CONVERT(nvarchar(6), DATA_EMBARQUE, 112) = '2017-01' --substituir por @ano_mes_param
 GROUP BY NOME_CLIENTE

Another alternative would be to get the first positions of the date for comparison using the LEFT() :

WHERE LEFT(CONVERT(varchar, DATA_EMBARQUE, 112), 6)

EDITED

After commenting on this answer () "Encapsulating column with function is not a good practice because it makes the code non sargable." See article #, I believe the best solution would be as follows (taking into account do not use functions in the where ):

DECLARE @DATA_INI DATE, @DATA_FIM DATE

SELECT @DATA_INI = '2018-01-01'
SELECT @DATA_FIM = EOMONTH(@DATA_INI)

SELECT NOME_CLIENTE, 
       COUNT(NUMERO_BILHETE) AS 'QUANTIDADE DE BILHETES' 
  FROM BANCO.DBO.CLIENTES
 WHERE DATA_EMBARQUE >= @DATA_INI
   AND DATA_EMBARQUE <= @DATA_FIM
 GROUP BY NOME_CLIENTE

I used EOMONTH () to be able to pick up the last day of the month; but you can also pick up the 1 day of the next month and check if DATA_EMBARQUE < @DATA_FIM (for this you would use DATEADD(month, 1, DATA_EMBARQUE) ).

This is a solution that only reports the month and year and the code will install the filter.

-- código #1 v2
declare @Ano smallint, @Mês tinyint;

-- informe o ano (formato aaaa) e mês (formato mm)
set @Ano= 2017;
set @Mês= 1;

---
-- geração automática das variáveis de filtro
declare @D1 date, @D2 date;
set dateformat dmy;
set @D1= cast ('1/' + cast (@Mês as varchar(2)) + '/' + cast (@Ano as char(4)) as date);
set @D2= dateadd (day, -1, dateadd (month, +1, @D1));

--
SELECT NOME_CLIENTE, 
       count (NUMERO_BILHETE) as [QUANTIDADE DE BILHETES]
  from BANCO.DBO.CLIENTES
  where DATA_EMBARQUE between @D1 and @D2
  group by NOME_CLIENTE;

To avoid implicit conversion, the variables @D1 and @D2 must be declared with the same data type as the DATA_EMBARQUE column. Details on the article The dangers of implicit conversion (1) .

Just filter the dates between the first and last day of the month. You do not need to use functions to filter month and year and you can use this logic.

--Primeiro dia de Janeiro (01/01/2017) até o último dia de Janeiro (31/01/2017)
SELECT NOME_CLIENTE, 
       COUNT(NUMERO_BILHETE) AS 'QUANTIDADE DE BILHETES' 
  FROM BANCO.DBO.CLIENTES
 WHERE DATA_EMBARQUE BETWEEN '2017-01-01' AND '2017-01-31'
 GROUP BY NOME_CLIENTE

If you want to use functions for this purpose, it would look like this:

SELECT NOME_CLIENTE, 
       COUNT(NUMERO_BILHETE) AS 'QUANTIDADE DE BILHETES' 
  FROM BANCO.DBO.CLIENTES
 WHERE MONTH(DATA_EMBARQUE) = 1 
   AND YEAR(DATA_EMBARQUE) = 2017
 GROUP BY NOME_CLIENTE

MONTH(data) you filter by month and YEAR(data) filter by year.

SELECT NOME_CLIENTE, 
       COUNT(NUMERO_BILHETE) AS 'QUANTIDADE DE BILHETES' 
  FROM BANCO.DBO.CLIENTES
 WHERE YEAR(DATA_EMBARQUE) >= YEAR(@DataInicial) 
   AND MONTH(DATA_EMBARQUE) >= MONTH(@DataInicial)
   AND YEAR(DATA_EMBARQUE) <= YEAR(@DataFinal) 
   AND MONTH(DATA_EMBARQUE) <= MONTH(@DataFinal)
GROUP BY NOME_CLIENTE