Getting records of dates - no end date in some cases

Question

The database is structured as below.

-----------------------------------
| product | start_year | end_year |
-----------------------------------
| prod_1  |    2001    |   2005   |
| prod_2  |    2002    |   2010   |
| prod_3  |    2003    |          |
| prod_4  |    2004    |   2016   |
| prod_5  |    2005    |          |
| prod_6  |    2005    |   2015   |
-----------------------------------

Some fields "end_year" are empty, blank cell indicates the current year. I have a parameter year and I want to receive the records in which the year is between two dates (start_year and end_year).

I could use this query, but unfortunately there was a problem with empty cells:

select * from table where 2003 between start_year and end_year;

Answer 1

You can check if the start_year is greater than or equal to the searchyear and check if end_year is less than or equal to searchyear or end_year is NULL

DECLARE @SearchYear INT=2004

SELECT Product
FROM [YourTable]
WHERE @SearchYear >= start_year AND (@StartYear >= end_year  OR end_year IS NULL)

Answer 2

I think you want to get the rows with year between the start_date and end_date.

Assuming if start_year is less than the given year, it should be included, you can use coalesce like this:

select *
from your_table
where 2003 between start_year and coalesce(end_year, 9999); -- a large year value

Answer 3

Did you mean that you want the records whose end data column as no values and start date as values .

 SELECT  product FROM table WHERE start_year='2003' and(end_year  IS NULL OR end_year = '');