Look up a date between two database fields

Question

Hi I have a piece of code which looks up a year between to fields in the database which are year_start & year_end.

And here is my code.

  SELECT * from $tableName where '$year' BETWEEN start_year AND end_year; LIMIT $start, $limit

Now this seems to work fine except if the year is equal to the end_year value for example if i am searching for 2002 and the end year value is 2002, then it returns no results.

Any suggestions would be appreciated.

Thanks

Seems to me like you are looking for [MySQL “between” clause not inclusive?](http://stackoverflow.com/questions/5080824/mysql-between-clause-not-inclusive) — EmmanuelG, Nov 21 '12 at 16:39
`BETWEEN` should return the endpoint results. What are the data types and actual contents of `start_year` and `end_year`? Are they DATETIMEs? — Michael Berkowski, Nov 21 '12 at 16:39

Pierre de LESPINAY · Accepted Answer · 2012-11-21T16:54:35.290

If start_year and end_year type is

INT

$sql = '
SELECT *
FROM '.$tableName.'
WHERE TRUE
  AND '.$year.' BETWEEN start_year AND end_year
LIMIT '.$start.', '.$limit.'
;
';

DATE

$sql = '
SELECT *
FROM '.$tableName.'
WHERE TRUE
  AND '.$year.' BETWEEN YEAR(start_year) AND YEAR(end_year)
LIMIT '.$start.', '.$limit.'
;
';

VARCHAR
to need to cast them

$sql = '
SELECT *
FROM '.$tableName.'
WHERE TRUE
  AND '.$year.' BETWEEN
    CAST(start_year AS UNSIGNED) AND CAST(end_year AS UNSIGNED)
LIMIT '.$start.', '.$limit.'
;
';

Munchies · Answer 2 · 2012-11-21T16:47:34.277

Just compare start_year and end_year before executing the query.

// If they're the same,
If start_year == end_year{
  select * from $tableName where '$year' = start_year; LIMIT $start, $limit;
  // more code
}

// if not, use your query
else{
  SELECT * from $tableName where '$year' BETWEEN start_year AND end_year; LIMIT $start, $limit;
  // more code
}

Not the prettiest solution but still a nice workaround.

Look up a date between two database fields

2 Answers2