jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes comparing dates in db2 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "comparing dates in db2" Watch "comparing dates in db2" New topic
Author

comparing dates in db2

vivien siu
Ranch Hand

Joined: Nov 10, 2005
Posts: 143
Hello guys,

I need to select 2 dates from a table, one is old start date, another is old end date where the condition matches certain columns.

after that, I want to check whether my values (new start date & new end date) is it overlapping the days between old start date and old end date.

the current logic i can think of is the new start date must be after the old end date, or the new end date is before the old start date. Somehow the sql query below is not working:



can anyone tell me how?


I'm not available, my BF's name is WORK.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Either new-start-date must be between promo-start-date and promo-end-date, or new-end-date must be between promo-start-date and promo-end-date. At least if my meaning for "overlapping" is the same as yours. (Mine permits the new date range to be entirely within the old date range.)
vivien siu
Ranch Hand

Joined: Nov 10, 2005
Posts: 143
mine is more complicated compared to just overlap. new start date or new end date cannot be in between of old start date and old end date, that's one, another thing is the new start date is before the old start date and also the new start date is after the old end date. so it's like the old period of time becomes a subset of the new period of time.

man this is so complicated,
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

I suggest drawing pictures with a number line. The old dates are an interval on the line and so are the new dates. Try to identify which of the six different possibilities you are interested in.
vivien siu
Ranch Hand

Joined: Nov 10, 2005
Posts: 143
hi this is my solution for date range

first I check whether the new start date or new end date is between the old start date and old end date.


then I check whether the old start date or old end date is between the new start date and new end date.


if either one yields a true result, then means both old date range and new date range is overlapped
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: comparing dates in db2