CODEWITHSUNDEEP
mysql
Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5246

How select all values between 2 date by year, month, day columns?

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)

Upvotes: 1

Views: 1504

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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'

Upvotes: 2

Nick
Nick

Reputation: 147196

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

Upvotes: 2

Related Questions