Ok ho risolto.
Se servisse a qualcuno, la sintassi corretta è:

SELECT * FROM tabella WHERE data BETWEEN cast('2007-12-18' as datetime) AND cast('2007-12-21' as datetime)

Ciaooo