Otácio, the error message indicates line 27 but in the code that transcribed there are 14 lines ...
If the V.DATA column is declared as datetime, it seems to me that the cause is related to the LANGUAGE of the session. One solution, which works regardless of how LANGUAGE and / or DATEFORMAT is configured, is to use the Convert () function. It can not be the Cast () function, because it is context sensitive.
And, to make code maintenance easier, I suggest you create variables to store the values of the period to query.
-- código #1
declare @DataInicial datetime, @DataFinal datetime;
-- informe aqui as datas limite, no formato dd/mm/aaaa
set @DataInicial= Convert(datetime, '1/4/2016', 103);
set @DataFinal= Convert(datetime, '30/9/2016', 103);
--
SELECT
ROW_NUMBER() OVER(ORDER BY V.DATA ASC) AS ID,
V.CHAPA AS CHAPA,
F.NOME AS NOME,
V.DATA AS DATA,
CASE WHEN V.BATIDA IS NULL THEN 0 ELSE V.BATIDA END AS FOLGA
FROM
ARELBATIDATRANSITOVIEW AS V
LEFT JOIN V_DADOSFUNC AS F ON V.CHAPA = F.CHAPA
WHERE
V.CHAPA = 123
AND V.DATA BETWEEN @DataInicial and @DataFinal
GROUP BY V.CHAPA,V.DATA,F.NOME,V.BATIDA;
In code # 1, style 103 was used in the Convert () function, which means that the date is given in dd / mm / yyyy format. This makes it easier to keep the code as it is our day to day format. Because the @DateInitial and @DateFinal variables are declared as datetime, the comparisons are all performed as datetime values.
