Consult sql with 4 dates and verify that the dates do not cross?

Question

Consult sql with 4 dates and verify that the dates do not cross?

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a table with these fields

We want to bring the id's of cars that are available in a range of dates that I receive from two inputs that would be when they want to take the car and when they want to return it. This dates I compare with the columns DateOutDay and DateTimeRegress.

select idAuto from contratos 
where (CAST(fechaHoraRegreso AS DATE) < '2017-09-11')
and ((CAST(fechaHoraSalida AS DATE) > '2017-09-12') or (CAST(fechaHoraSalida 
AS DATE) < '2017-09-12'))
)

The problem is that there are dates that are not in the range and does not show them

mysql sql

asked by El_Crack_Viruz El_Crack_Viruz 08.09.2017 в 19:25

source

2 answers

"Same" consultations, different results Pass different DataTable to the same function in VB

score 0 · Answer 1

You can solve it like this:

SELECT idAuto 
       FROM contratos 
       WHERE CAST(fechaHoraSalida AS DATE) < '2017-09-11'
             OR CAST(fechaHoraRegreso AS DATE) > '2017-09-12'

Obviously I assume that fechaHoraSalida and fechaHoraRegreso and the period requested (Date From / DateUp) are consistent. What we do is recover all the contratos whose Date of Departure is less than our DateOf O whose Date of Return is greater than our DateUp.

score 0 · Answer 2

Thanks to all I found a possible solution that is this:

select idAuto from contratos 
where 
(CAST(fechaHoraSalida AS DATE)  between '2017-09-22' and  '2017-09-25' )
or
( CAST(fechaHoraRegreso AS DATE)  between '2017-09-22' and '2017-09-25')
or
((CAST(fechaHoraSalida AS DATE)<'2017-09-22')
and
(CAST(fechaHoraRegreso AS DATE)> '2017-09-22'))
or
((CAST(fechaHoraRegreso AS DATE)>'2017-09-25')
and
(CAST(fechaHoraSalida AS DATE)< '2017-09-25'))

If someone has any other suggestions, they are welcome