SQL dates

[adsl2]

Hi all,

I am struggling a bit with this SQL statement. I have a table with two dates , collect and return. Now it is easy to test if one date falls outside collect and return by using not between. But what do I do if I need to test if two dates fall outside between collect and return. Example: “SELECT * FROM TBLBookings WHERE (BETWEEN :a AND :b) NOT BETWEEN collect AND return”. I get an error at the first between.

Basically not :a, :b or any date between :a or :b may be the same as collect or return or fall between them.

Any help will be appreciated

Thanks

 

[cyberbob1979]

You could do something like :

Select * from TBLBOOKINGS
where {date} < a or {date} > b

Replacing {date} with your field name or variable.

Just a quick thought

 

[cyberbob1979]

You could also check the dates with a case statement and use the results in another formula

 

[bin3]

I'm a bit rusty, but shouldn't it be something like

SELECT * FROM TABLE t WHERE (t.SOMEDATE BETWEEN :a AND :b) AND(t.SOME_DATE NOT BETWEEN t.collect AND t.return)

Or something like that.

I.e. Give the between something to compare against: You need 3 for each between: That which you compare, an upper and a lower limit.

 

[cyberbob1979]

After reading the question again - you could do it like this:

Select * from Table
where ({date1} < a and {date 2} < a) or ({date 1 > b and {date 2} > b)

 

[bin3]

Could also try self joins, inner selects, etc, etc: Really a few ways to do it.

Dirty but workable ...

select * from blah where ( huh ) and blah.id not in (select id from blah where duh) ...

etc, etc.

 

[adsl2]

Thank you very much for all the help guys, really appreciated!

 

[nakedpeanut]

Let me get this right, you have two dates coming in, like in a stored proc and want to ensure that they do not contain collect and return in their period.

@start_date
@end_date

-- Do some checks to ensure they are correct
-- eg @start_date IS NOT NULL and  @start_date < @end_date etc..

SELECT  * 
FROM TBLBookings 
WHERE 
[INDENT](collect < @start_date AND return < @start_date) [/INDENT]
[INDENT]OR[/INDENT]
[INDENT](collect > @end_date AND return > @end_date)[/INDENT]