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)
).
(1) What does it mean "the day does not influence in my search"? (2) How the column is declared
DATA_EMBARQUE
?– José Diz
It is as DATE. I wanted to use as parameters only the month and the year to perform the search.
– Caroline Telles
@Carolinetelles, any of the answers solved? what needs to be improved?
– rLinhares