0

I've a table where there are 8 columns and 2 of them are To & From these are having datatype of datetime. I want to execute a select query with some filter on those two fields.

Now see the following example

Record X = From "5 May" to  "18 May" 

Filter 1: from "5 May" to "5 May" … x should show 

Filter 2: from "10 May" to "10 May" … x should show

Filter 3: from "1 May" to "1 May" … x should NOT show

Filter 4: from "19 May" to "19 May" … x should NOT show

Filter 5: from "18 May" to "18 May" … x should show 

Filter 6: From "4 May" to  "17 May" ….  X should show

Filter 7: From "4 May" to  "18 May" ….  X should show

Filter 8: From "4 May" to  "19 May" ….  X should show

Filter 9: From "5 May" to  "17 May" ….  X should show

Filter 10: From "5 May" to  "18 May" ….  X should show

Filter 11: From "5 May" to  "19 May" ….  X should show

Filter 12: From "2 May" to  "3 May" ….  X should NOT show

Filter 13: From "20 May" to  "25 May" ….  X should NOT show

So Basically the logic is if any date are common in between the filter date range and the database date range then the record should show in result.

Can you please help me? I'm confused after seeing all these configuration.

Krishanu Dey
  • 6,326
  • 7
  • 51
  • 69
  • You really should make that more clear by putting it as actual tabular data, instead of these explanation columns. Especially given your supposed datatype. And I assume you only want a specific year, too, yes? – Clockwork-Muse May 04 '14 at 00:24
  • Duplicate of [Comparing date ranges](http://stackoverflow.com/questions/143552/comparing-date-ranges) (knew I'd seen these around somewhere). As you seem to be dealing with a timestamp, I'd recommend using an exclusive upper-bound (`<`) instead, for [several reasons detailed on this blog post](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). – Clockwork-Muse May 04 '14 at 03:08

3 Answers3

1
SELECT *
FROM YourTable
WHERE from >= '5/5/2014'
  AND from < '5/6/2014'
  AND to >= '5/5/2014'
  AND to < '5/6/2014'

Wouldn't be as simple as that for filter 1? Or do you need all filters in 1 query?

Frank
  • 658
  • 1
  • 8
  • 12
1

You need either a from or to column to be in the range defined by the filter, inclusive:

declare @filter_from datetime, @filter_to datetime

select *
from <table>
where col_from between @filter_from and @filter_to
  or col_to between @filter_from and @filter_to
  or @filter_from between col_from and col_to
  or @filter_to between vol_from and col_to
dean
  • 9,960
  • 2
  • 25
  • 26
  • Especially because this is SQL Server, [he's going to want to avoid `BETWEEN`](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) – Clockwork-Muse May 04 '14 at 00:25
  • 1
    No, he won't, despite what some blog on the internet said, `between` is jusy a benign operator and a perfect fit in this particular situation. – dean May 04 '14 at 04:55
  • Actually, `BETWEEN` is **not** a perfect fit here, because: 1) As the OP's stated type is `DATETIME`, he'd need to do some convoluted work to safely use `BETWEEN` and 2) The range provided isn't the correct one (your answer misses `Filter# 8`, given his search criteria) - check the answer in the linked duplicate. Personally, I think `BETWEEN` should be avoided because it promotes a dangerous/wrong way of thinking: that there is such a thing as a distinct ending value. This applies to everything except integral types! ....where it should then be avoided for consistency. – Clockwork-Muse May 04 '14 at 05:13
  • The OP's clearly using `datetime` without a time portion; however, `between` is just a shorthand for `x >= a and x <= b', so if he stores time portion with datetime and uses only date portion, he'll have to do some work to get rid of it anyway. You were right that my answer wasn't correct, but it had nothing with between being bad, but rather with me being stupid, thx for pointing it out. Your last argument is one worth discussing though. – dean May 04 '14 at 05:24
  • Hmmm? Er, the OP left of the _year_, so we don't know what he has stored. In the blog, the recommendation is for an exclusive upper-bound (`<`), which means **no** extra work. It's clear you didn't read the linked question/answer, because you only need two conditions (as opposed to eight). – Clockwork-Muse May 04 '14 at 05:31
0

Try this 

Select * from table_name where startDate between '4 May' and '18 May'
And endDate between '5 May' and '19 May'

Thanks.Hope this helps

Monika
  • 135
  • 1
  • 12
  • Excdept, if he's dealing with a `datetime` (timestamp) type, [he _really_ wants to avoid `BETWEEN`](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). You're on the right track, though. – Clockwork-Muse May 04 '14 at 00:17