AndreaNobili AndreaNobili - 1 month ago 4x
SQL Question

Why I obtain this "Incorrect syntax near the keyword 'between'" into a SQL Server query that use between?

I am not familiar with databases.

I am working on Microsoft SQL Server and I have some problem trying to perform this query that uses between keyboard to select a Date range.

My query is:

select NumeroPolizza ,sum(v.Ctv) as Ctv_RI
from (
select r.NumeroPolizza,SUM(r.ImportoPrestazioneIniziale) as Ctv
from Prestazione r with(nolock)
where r.NumeroPolizza in (select ID from Polizza p with(nolock) where TipoSistemaProvenienzaID=8)
--and r.DataInizio <= '2015-12-31'
and between '2016-01-01' and '2016-04-01'
group by r.NumeroPolizza


select NumeroPolizza,SUM(ImportoRivalutazioneDaPiano+ImportoRivalutazioneEstemporaneo)as Ctv
from Rivalutazione with(nolock)
where NumeroPolizza in (select ID from Polizza p with(nolock) where TipoSistemaProvenienzaID=8)
--and DAtaDecorrenza <= '2015-12-31'
and between '2016-01-01' and '2016-04-01'
group by NumeroPolizza
) v
group by NumeroPolizza
order by NumeroPolizza

As you can see I am using 2 between as filter of 2 where conditions, something like this:

and between '2016-01-01' and '2016-04-01'

the problem is that SQL Server give me the following error message:

11:30:36 [SELECT - 0 row(s), 0.000 secs] [Error Code: 156, SQL State: S0001] Incorrect syntax near the keyword 'between'.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

What am I missing? How do I fix this issue?


You miss the column Name before between.

Syntax is <column_name> between <value> and <other_value>