Checking for dates between to values

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lloydie-t
Forum Commoner
Posts: 88
Joined: Thu Jun 27, 2002 3:41 am
Location: UK

Checking for dates between to values

Post by lloydie-t »

I am working on a calendar project and I am a stuck. I have a table where there are two datetime columns for time start and time end. The problem I am have is querying those columns to check that dates I am about to add do not conflict with existing dates.

for instance:
I may have a row with a time_start = 2003-07-21 08:00:00
and time_end = 2003-07-22 11:30:00

I now want to do a query to check if new_time_start = 2003-07-22 06:00:00 and new_time_end = 2003-07-22 12:30:00 conflicts with the above row

I have been ages trying to get this to work with no joy. Knowing my luck it's something simple, but I will appreciate the help anyway.
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

WHERE ('$new_start' > time_end OR '$new_end' < time_start)

or some flavor and variation of that..
lloydie-t
Forum Commoner
Posts: 88
Joined: Thu Jun 27, 2002 3:41 am
Location: UK

Post by lloydie-t »

I have already tried that and in instances WHERE $new_start' = 2003-07-21 06:00:00 and '$new_end' = 2003-07-22 12:30:00 the formula whould fail to work even though this time period is overlaping. At the moment using these variations I seem to be getting all records or nothing, but not the required.
lloydie-t
Forum Commoner
Posts: 88
Joined: Thu Jun 27, 2002 3:41 am
Location: UK

Post by lloydie-t »

Stoker, I clearly did'nt look at what you posted earlier, cause what seems to be working is a variation the same thing. If any body can see any holes let me know, although I have yet to find any.

WHERE time_end >'$new_start'OR time_start >'$new_end'
Post Reply