Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    1

    Smile Unanswered: check user supplied date range is in between startdate and enddate

    Hello friends,

    I need query which will check user supplied date range is in between the existing table startdate and enddate.
    if any of the date of user supplied date range is in between the tables start date and end date,it should retrun that record from table.

    for example user supply date range is from 1 may 2012 to 5 may 2012.
    then query must check that
    1 may 2005
    2 may 2005
    3 may 2005
    4 may 2005
    5 may 2005
    (all dates) is in between startdate and enddate of existing table.

    please reply...

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    I do not know if I understand correctly but try this:

    Code:
    select * from MyTable
    where exists (select 1 from MyTable
                  having (DATEADD(year, YEAR(GETDATE()) - YEAR(MIN(MyDateColumn)), MIN(MyDateColumn)) between @StartDate and @EndDate) and
                         (DATEADD(year, YEAR(GETDATE()) - YEAR(MAX(MyDateColumn)), MAX(MyDateColumn)) between @StartDate and @EndDate) )
    Hope this helps.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RAHUL PATEL View Post
    if any of the date of user supplied date range is in between the tables start date and end date...
    the key part of this sentence is any of the date

    it would seem that you are interested also in partial overlaps

    read this thread, starting at post #14

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

Tags for this Thread

Posting Permissions

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