Good afternoon,
Is there any way to exclude a period without using BETWEEN ?
x NOT BETWEEN HorFim AND HorIni
and y NOT BETWEEN HorFim AND HorIni
Where:
x = hora inicial digita pelo usuario.
y = hora final digita pelo usuario.
I have to make this sql work without BETWEEN , the problem is that my final time ( HorFim ) is less than the start time ( HorIni )
Turn the DATETIME into DATE to remove the time and use the maior que and menor que operators:
SELECT *
FROM tabela
WHERE (CAST(x AS DATE) < CAST(HorIni AS DATE) OR CAST(x AS DATE) > CAST(HorFim AS DATE))
AND (CAST(y AS DATE) < CAST(HorIni AS DATE) OR CAST(y AS DATE) > CAST(HorFim AS DATE))
I also noticed that the order of your BETWEEN was reversed. The above condition can be changed to:
SELECT *
FROM tabela
WHERE CAST(x AS DATE) NOT BETWEEN CAST(HorIni AS DATE) AND CAST(HorFim AS DATE))
AND CAST(y AS DATE) NOT BETWEEN CAST(HorIni AS DATE) AND CAST(HorFim AS DATE))