Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2003
    Location
    France
    Posts
    21

    Question Unanswered: how to compare date periods ?

    Hi,
    I need to compare 2 periods (start date / end date) in order to find out if the first period overlaps or in included in the second period.

    any idea ???

    thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    where [P1].[StartDate] <= [P2].[EndDate]
    and [P1].[EndDate] >= [P2].[StartDate]
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman, that's only a complete overlap (2nd period completely within the first)

    what about partial overlaps

    july 15 to august 15 partially overlaps august 1 to august 31
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2004
    Posts
    125
    What about:

    Code:
    select case 
    when P1.Startdate between P2.startdate and P2.Enddate and P1.Enddate between P2.startdate and P2.Enddate then 'Include'
    when P1.Startdate between P2.startdate and P2.Enddate or P1.Enddate between P2.startdate and P2.Enddate then 'OverLaps'
    else 'Does not Overlap or is included'
    end

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice, DMWCincy, but you're missing blindman's conditions

    this topic has been covered before, e.g. http://www.dbforums.com/t1147226.html, but no point looking that up, because this site has lost its archives for old posts (which is one of the reasons i'll be leaving shortly)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by r937
    blindman, that's only a complete overlap (2nd period completely within the first)

    what about partial overlaps

    july 15 to august 15 partially overlaps august 1 to august 31
    Actually, blindman's logic is correct and will handle partial overlaps such as the scenario you specified as well as complete overlaps.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by r937
    blindman, that's only a complete overlap (2nd period completely within the first)

    what about partial overlaps

    july 15 to august 15 partially overlaps august 1 to august 31
    Since blindman is offline, I'll note that I use the same technique, and it will find partial overlaps, including your sample. Substituting your values into his WHERE clause:

    where july 15 <= august 31
    and august 15 >= august 1

    You can see that your sample record would be returned by his WHERE clause.

    Edit: Sorry RogerWilco, your post wasn't there when I started typing.
    Paul

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RogerWilco
    Actually, blindman's logic is correct .
    aaargh, you're right, i read it wrong

    sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, now I don't get to post some snooty and tactless comment to Rudy about how my code DOES work, 'cause you guys already took all the fun away.

    --Humph!--
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's okay, i feel sufficiently blindman-chastised already
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2003
    Location
    France
    Posts
    21

    Smile !!

    I can't believe it was so easy !
    thanks blindman

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    this topic has been covered before, e.g. http://www.dbforums.com/t1147226.html, but no point looking that up, because this site has lost its archives for old posts (which is one of the reasons i'll be leaving shortly)
    Sorry to hear you will be leaving us rudeboy.

    But I just can't help myself - don't you keep copies of posts like this?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i do

    and how did you know rudeboy was one of my nicknames?

    will i be seeing you on one of the other sites i mentioned in this thread?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    and how did you know rudeboy was one of my nicknames?
    I imagine your friends have the same colossal imagination as I

    Quote Originally Posted by r937
    will i be seeing you on one of the other sites i mentioned in this thread?
    Ever so kind of you to ask - I must admit Tek Tips regularly comes up on Googles - wish they would get rid of the little drop-down-over-the-text-you-are-reading-just-as-it-gets-interesting box. I might indeed have a look - as you know I have a penchant for ANSII SQL also.

    I quite like playing around on this site (another that came up regularly on searches, so I joined). They give you points and from these you are assigned worth. It's ever so self validating. I'm not sure that there are the sort of heavy weights in residence as there are on this forum though.

    I see from your link a lot of you are going to be moving. I may not post in this forum often but I visit frequently - I've learnt lots and have been tremendously entertained, expecially when pickings are poor and you are forced to turn on one another

    I hope you all do well - drop us a post in the baby db forum (AKA Access) to let us know your new roaming grounds.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    ...They give you points and from these you are assigned worth. It's ever so self validating.
    devshed forums are like that, you build up "rep points" (reputation) which you can then spread around to others

    see http://forums.devshed.com/member.php?userid=22396
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •