Filter using only month and year on SQLSERVER

Question

I need to mount a select, in which the filter is used only month and year in the parameters.

This is the select I’m currently using, but I need the 'day' not to influence my search.

SELECT NOME_CLIENTE, 
       COUNT(NUMERO_BILHETE) AS 'QUANTIDADE DE BILHETES' 
  FROM BANCO.DBO.CLIENTES
 WHERE DATA_EMBARQUE BETWEEN '2017-01-15' 
   AND '2017-01-30'  
 GROUP BY NOME_CLIENTE

Answer 1

I believe the simplest way is to use the correct formatting of the kind date (here has a list of codes and formats usable 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 take the first positions of the date for comparison using the LEFT():

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

---

EDITED

after comment on this reply ("Encapsulating column with function is not a good practice as it makes the code non sargable. See article Building Efficient T-SQL Code: Sargability"), I believe that the best solution would be the following (taking into account do not use Where functions):

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 the EOMONTH() so you can catch the last day of the month; but you can also catch the day 1 of the following month and check whether DATA_EMBARQUE < @DATA_FIM (for that, I would use DATEADD(month, 1, DATA_EMBARQUE)).

Answer 2

Here is a solution that informs only the month and year and the code takes care of mounting 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, variables @D1 and @D2 shall be declared with the same data type as in the column DATA_EMBARQUE. Details in the article The dangers of implicit conversion (1).

Answer 3

Just filter the dates between the first and last day of the month. No 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, would be 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) filters by the year.

Answer 4

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