check date range in between date range

[RA UL PATEL]

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...Thanx in advance...

[Blue_Boy]

select * from tablename where startdate>=@startDate and enddate<=@endDate

Initialize parameters

@startDate

and

@endDate

with values

---

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.

[jschell]

Blue_Boy wrote:

where startdate>=@startDate and enddate<=@endDate

Probably not a good idea. One end should be inclusive and the other exclusive, for example. where startdate>=@startDate and enddate<@endDate The reason for this is that for a range check you do not want the same value to show up twice in two 'different' ranges.

[Vishal D Kolekar]

SELECT * FROM tablename where insertdate BETWEEN @startDate and @endDate this is simple but it won't contain @endDate in result set. For that you need to use DATEADD(datepart, number, date) fuction Since your query will be SELECT * FROM tablename WHERE insertdate BETWEEN @startDate and DATEADD(d,1,@endDate) Thanks, Vishal K

[vvashishta]

Instead of usign Between Operator, you should use FromDate >= @FromDate AND EndDate < DateAdd(D, 1, @EndDate ) (It always perform the accurate process, specially in case when Date Column accepts dates also) Remember EndDate must work with LessThan and FromDate performs on GreaterThanEqualTo

- Happy Coding - Vishal Vashishta