compare date, and timespan

[darkstar]

Hi,

I'm searching for a SQL-command for a travelagency that selects all the Rooms that are occupied in a certained timespan (I have a begindate and a end date)

but now is my question: how can i compare a period (with begin and end date) with another period?

hope you can help!

[teixeira]

Hi,

can u be a little more specific in your comparison?

maybe you already have something like this
SELECT ..... FROM ...... WHERE initialDate>'02/11/2006' AND finalDate<'02/01/2007'

Is this waht u have? Can you describe what you really wanna do with comparing 2 periods of dates?

Regards,
Teixeira

[darkstar]

SELECT DISTINCT tblRoom.RoomID
FROM tblRoom INNER JOIN tblRoomReservation ON tblRoom.RoomID = tblRoomReservation.RoomID
WHERE (((tblRoomReservation.Begindate)<#5/1/2007#) AND ((tblRoomReservation.Enddate)>#5/1/2007#)) OR (((tblRoomReservation.Begindate)<#5/8/2007#) AND ((tblRoomReservation.Enddate)>#5/8/2007#));



This is my sql command that i allready had!
The problem with this command is that it only checks of the begin and end date are between the two dates that the room allready has been reserved!

[rmiao]

What do you mean 'compare a period (with begin and end date) with another period'? Have sample?

[darkstar]

What do you mean 'compare a period (with begin and end date) with another period'? Have sample?

The values, #5/1/2007# and #5/8/2007#, are the variables that can be changed, and this values will be implemented with VB.net

So these values should be the begin and end date of a period (for explaining i will call this 'period 1')

Then i search in my database for all the reservations where i also have a begin and end date ! Then i want to check of the period of the reservation is not in 'period 1'!

[rmiao]

Tried replace OR with AND?

[darkstar]

Tried replace OR with AND?

yes, but then it returns a wrong result