Tukukkk

In MySQL database, I have table with YEAR, MONTH, DAY columns.

| YEAR | MONTH | DAY | VALUE |

|----------------------------|

| 2018 | 11    | 9   | 1056  |

| 2018 | 11    | 10  | 6582  |

|****************************|

| 2018 | 12    | 9   | 6892  |

| 2018 | 12    | 10  | 5987  |

|****************************|

| 2019 | 3     | 5   |  5693 |

| 2019 | 3     | 6   |  5698 |

I need to take all values from the table between 2 date.

Let's say between 2018-11-09 and 2019-03-05 or between 2018-11-10 and 2018-12-09.

I need to say that unfortunately I can't merge these three column for one datetime column. Also the table has partitioning by that 3 columns.

Datatype of columns: smallint(6)

It seems this query should give you the results you want. It creates a date string out of your 3 columns and then uses STR_TO_DATE to convert that into a value that MySQL can compare with the search date strings.

SELECT *

FROM table1

WHERE STR_TO_DATE(CONCAT_WS('/', `DAY`, `MONTH`, `YEAR`), '%d/%m/%Y') BETWEEN '2018-11-09' AND '2019-03-05'

Output

YEAR    MONTH   DAY     VALUE

2018    11      9       1056

2018    11      10      6582

2018    12      9       6892

2018    12      10      5987

2019    3       5       5693

Demo on dbfiddle

One way is to create a string in MySQL Date format (YYYY-MM-DD) using string functions such as Concat() and Lpad():

SELECT *

FROM your_table

WHERE CONCAT(`YEAR`, '-', LPAD(`MONTH`,2,'0'), '-', LPAD(`DAY`,2,'0')) 

        BETWEEN '2018-11-09' AND '2019-03-05'

Based on further discussion in comments, if you can input the year, month, and day value separately for the given data range(s); instead of creating a date using functions, we can directly use the respective columns instead. This will also allow us to utilize indexes (if defined) on these columns.

SELECT *

FROM your_table

WHERE 

  /* Condition for the month in the start date of the range */

  (YEAR = 2018 AND MONTH = 11 AND DAY >= 9)

    OR

  /* Condition for the rest of the months in start date year */

  (YEAR = 2018 AND MONTH > 11)

    OR 

  /* Condition for the month in the end date of the range */

  (YEAR = 2019 AND MONTH = 3 AND DAY <= 5)

    OR

  /* Condition for the rest of the months in end date year */

  (YEAR = 2019 AND MONTH < 3)

    OR

  /* Condition for the years between the start and end date */

  (YEAR > 2018 AND YEAR < 2019)

Above mentioned conditions can be compressed further. But I have written in this manner, for ease of understand-ability.

However, it is recommended to create another column to store the date in Date format. If you cannot make changes to the application code, and if your MySQL version >= 5.7, you can look at Generated Columns, and refer to that in your SELECT query instead.

ALTER TABLE your_table

ADD COLUMN date_col DATE 

GENERATED ALWAYS AS CONCAT(`YEAR`, '-', LPAD(`MONTH`,2,'0'), '-', LPAD(`DAY`,2,'0')) STORED;

Then, the SELECT query becomes trivial:

SELECT * FROM your_table

WHERE date_col BETWEEN '2018-11-09' AND '2019-03-05'