• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

comparing dates in db2

 
vivien siu
Ranch Hand
Posts: 143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic