Banalmente:

codice:
WHERE (now() BETWEEN a.dtInizioManifestazione AND a.dtFineManifestazione) AND 
      (a.dtEliminaManifestazione IS NULL OR a.dtEliminaManifestazione > now())
Poi vedi tu se usare il maggiore o il maggiore uguale in base alle tue esigenze.

Ciao.