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

Question

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

    UNION

    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?

Answer 1

You miss the column Name before between.

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