Find records IN BETWEEN Date Range

Question

Please see attached image

I have a table which have FromDate and ToDate. FromDate is start of some event and ToDate is end of that event. I need to find a record if search criteria is in between range of dates.

e.g.

If a record has FromDate 2010/15/5 and ToDate 2010/15/25 and my criteria is FromDate 2010/5/18 and ToDate is 2010/5/21 then this record should be in search results because this is in the range of 15 to 25.

Following is my search query (chunk of)

SELECT   m.EventId
FROM     MajorEvents 

WHERE   (   (m.LocationID = @locationID OR @locationID IS NULL) OR M.LocationID IS NULL)
AND      (
            CONVERT(VARCHAR(10),M.EventDateFrom,23) BETWEEN  CONVERT(VARCHAR(10),@DateTimeFrom,23) AND CONVERT(VARCHAR(10),@DateTimeTo,23) 
            OR
            CONVERT(VARCHAR(10),M.EventDateTo,23) BETWEEN CONVERT(VARCHAR(10),@DateTimeFrom,23) AND CONVERT(VARCHAR(10),@DateTimeTo,23)
        )

If Search Criteria is equal to FromDate or ToDate then results are ok e.g. If search criterai is DateFrom = 2010/5/15 AND DateTo = 2010/5/18 then this record will return becasue Date From is exactly what is DateFrom in db.

OR

If search criteria is DateFrom = 2010/5/22 AND DateTo = 2010/5/25 then this record will return because Date To is exactly what is DateTo in db

But if anything in between this range it does not work

Thanks for the help.

Edit:

I can not use <= or > because this will bring all other records which are less than and greater than search criteria dates.

I just want to fetch those records which are on those dates E.g. FromDate = 2010/5/15 and DateTo = 2010/5/25 This is date range but event is on all dates in between so one solution is that I store all dates from 2010/5/15 to 2010/5/25 in separate table but if I can do this using query?

You can also tell me that it is not possible.

Answer 1

Why don't you use an ISO format when converting to strings, having said that, your query will perform very badly because it is not SARGable, please read How Does Between Work With Dates In SQL Server? it will show you that you have to use where EventDate >= ... AND EventDate < ....

Answer 2

What about this:

SELECT   m.EventId
FROM     MajorEvents AS m
WHERE   (   
                ((m.LocationID = @locationID) OR (@locationID IS NULL))
            OR  (m.LocationID IS NULL)
        )
    AND (
                (DATEADD(DAY, DATEDIFF(DAY, 0, m.EventDateFrom), 0) <= DATEADD(DAY, DATEDIFF(DAY, 0, m.@DateTimeTo), 0))
            AND (DATEADD(DAY, DATEDIFF(DAY, 0, m.EventDateTo), 0) >= DATEADD(DAY, DATEDIFF(DAY, 0, m.@DateTimeFrom), 0))
        )

Answer 3

To second NTSystemAnalyst's post, try this option:

SELECT m.EventId FROM MajorEvents AS m WHERE (
((m.LocationID = @locationID) OR (@locationID IS NULL)) OR (m.LocationID IS NULL) )

AND ( m.EventDateFrom < DATEADD(dd, 1, CONVERT(varchar(10), @DateTimeTo, 23)) AND m.EventDateTo > DATEADD(dd, -1, CONVERT(varchar(10), @DateTimeFrom, 23)) )