AndroidNewBee AndroidNewBee - 2 years ago 68
MySQL Question

How to write MySQL query where Date1 TO Date2?

Is there a way in mysql where I can do something like

Select * FROM table WHERE (YEAR(Date) = '$year1' AND MONTH(Date) =
'$month1') TO (YEAR(Date) = '$year2' AND MONTH(Date) = '$month2');

enter image description here

I am new to programming and would really appreciate any help or suggestions.Thank you.

Answer Source
FROM table
WHERE Date BETWEEN STR_TO_DATE('$year1 $month1', '%Y %m') AND
                   STR_TO_DATE('$year2 ($month2+1)', '%Y %m')

Actually, using BETWEEN might include the first day of the following month which you don't really want. In this case, you can just use > and < operators:

FROM table
WHERE Date >= STR_TO_DATE('$year1 $month1', '%Y %m') AND
      Date < STR_TO_DATE('$year2 ($month2+1)', '%Y %m')

From this resource we find that:

The STR_TO_DATE() sets all incomplete date values, which are not provided by the input string, to zero.

If you wanted to get the range February 1, 2016 to March 31, 2016 (inclusive), then my solution says take any date on or after February 1 and before April 1.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download