-3

(I've found the logic of the solution here: Determine Whether Two Date Ranges Overlap)

The table structure is: 

contracts[start_date, end_date]

My form uses two inputs, inp_start_date and inp_end_date. I want to use their values to select the contracts that are in the range.

Example (pseudo):

  1. a contract is valid between its start_date to its end_date.
  2. Get all contracts that are valid, between inp_start_date to inp_end_date

What is the best way to set the conditions for this query?

edit

Examples:
if my inp_start_date is 5/21/2005
and the inp_end_date is 9/21/2005
then it will select contracts that starts and ends BEFORE AND AFTER inp_start_date and inp_end_date, as long as it was valid at that time range, even partially.

The query will find all contracts that their date range, is intersecting with the inp_start and inp_end dates range.

Contract and inputs example: 

contract.start_date = 1.1.2001  |#|  contract.end_date = 5.31.2001
input.start_date = 10.21.2000   |#|  input.end_date = 2001.3.21

This contract should be caught, since at least a part of it is in the range.

Community
  • 1
  • 1
yossi
  • 3,090
  • 7
  • 45
  • 65
  • What is the question actually about? Do you know how to do that in *any way*? It's the basic comparison of 2 columns with `AND`. – zerkms Feb 20 '14 at 23:46
  • 2
    Dates can be compared just like anything else. Use, `<` or `>` or `BETWEEN`. If you have an issue implementing that, let us know with a more specific question and we can help you. – Damien Black Feb 20 '14 at 23:48
  • please re-read my question.. it's not a single date! within a range. it's a range within a range. check the edit please. – yossi Feb 21 '14 at 08:28

1 Answers1

1

Assuming you're validating your input and that it's in the correct format, you could try the BETWEEN clause or the >= and <= operators:

SELECT id 
FROM contracts
WHERE start_date >= <inp_start_date> AND end_date <= <inp_end_date>
Kermit
  • 33,827
  • 13
  • 85
  • 121
roycable
  • 301
  • 1
  • 9