| A ver si lo solucionamos,
Cuando filtras campos es necesario que la expresión de búsqueda tenga el mismo tipo de datos que el campo o expresión donde quieres buscar:
WHERE cns.Fecha BETWEEN CONVERT(DATETIME, '01/08/04') AND CONVERT(DATETIME, '01/08/04')
por otra parte, si sólo quieres buscar una fecha y no todas las comprendidas dentro de un intérvalo no es necesario el operador BETWEEN:
WHERE cns.Fecha = CONVERT(DATETIME, '01/08/04') |