devojuhyg devojuhyg - 3 years ago 58
MySQL Question

SQL query, check year on one column

I have a column in my MySQL table showing year.
Example:


Table: Information

ID | Year_sold
--- --------
1 | 2002-2010
2 | 2005-2015
3 | 2011-____
4 | 1975-1978


I will ask the table if it has data to show for a specific year.
Example:

SELECT * FROM Information WHERE Year_sold = '2012';


This will of course not work, but how can I type the SQL query if the result should be ID 2 and 3.

If the item is still active and being sold, the years will be shown like ID 3, "2011-____". I can replace the "____" if needed.

Answer Source

Use the BETWEEN, LEFT and SUBSTRING_INDEX functions.

SELECT ID, Year_sold
FROM Information
WHERE '2012' BETWEEN LEFT(Year_sold,4) AND SUBSTRING_INDEX(Year_sold,'-',-1)

Output

ID  Year_sold
2   2005-2015
3   2011-____

SQL Fiddle: http://sqlfiddle.com/#!9/7df7b7/1/0

If possible I would start again with your table structure and have something like:

Table: Information

ID | Year_sold_from | Year_sold_to
--- ------------------------------
1  | 2002           | 2010
2  | 2005           | 2015
3  | 2011           | null
4  | 1975           | 1978
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download