Date comparison

[jeeva]

hi frnds,

 

i have a problem that i cant able to write a query for comparing the two dates.

i have fields like reqfrom and reqto as DateTime format for conference room booking.Now the problem is new datetime should not conflict with previous datetime.

For example:

some one booked the room from 29-09-07 10:30 AM to  29-09-07 11:30 AM.now if i  booking the room from 29-09-07 9:30 AM to 29-09-07 12:30 PM then the system should not allow to store.

for this i have written the query like

select count(*) as cnt from conferencebooking where ('2007-09-29 12:01' between ReqFrom and ReqTo) or ('2007-09-29 15:59' between ReqFrom and ReqTo)

but it does not seems to work

 

any one can help me....... ?

[Barand]

[pre]

  Booking              ReqFrom        ReqTo

                          |              |

    A              [-------]          |

                          |              |

    B                    |          [----------]

                          |              |

    C                    |  [-------]  |

                          |              |

    D                [-------------------------]

                          |              |

    E        [---]      |              |

                          |              |

    F                    |              |          [---------]

[/pre]

 

Conflict occurs (A,B,C,D above) when

BookingEnd > ReqFrom AND BookingStart < ReqTo

where BookingStart and BookingEnd are existing times stored in DB