How can I fulfill the spacing condition correctly?
I made a request to display information according to the interval.
HERE TABLE
|id| |date_payment|
1 '2014-11-18'
2 '2014-12-18'
3 '2015-01-27'
5 '2015-03-20'
6 '2015-04-18'
7 '2015-05-27'
8 '2015-06-20'
9 '2015-07-18'
10 '2015-08-27'
11 '2015-09-20'
I am trying to show information using Year and Month
For example, using year = 2014 and month = 11 as dynamic values according to an interval of 4 months and should have this result:
|id| |date_payment|
1 '2014-11-18'
2 '2014-12-18'
3 '2015-01-27'
Here is my request:
SET @var_year = '2014';
SET @var_month = '11';
SELECT * FROM payments
WHERE date_payment = DATE_ADD('2014-11', INTERVAL 4 MONTH)
My query is down and showing results.
+3
source to share
2 answers
DATE_ADD INTERVAL 4 MONTH
just adds 4 months to your date ('2014-11-01')
This query will return what you expect
SET @var_year = '2014';
SET @var_month = '11';
SET @from = STR_TO_DATE(CONCAT(@var_year, '/', @var_month, '/01'), '%Y/%m/%d');
SET @to = DATE_ADD(@from, INTERVAL 4 MONTH);
SELECT * FROM payments
WHERE date_payment BETWEEN @from AND @to;
There is a live example here
http://sqlfiddle.com/#!9/013b6/42
Hope it helps
+1
source to share
SET @var_year = '2014';
SET @var_month = '11';
SELECT * FROM payments
WHERE date_payment = CONCAT(@var_year, '-', @var_month, '-01') + INTERVAL 4 MONTH;
Because:
SET @var_year = '2014';
SET @var_month = '11';
SELECT CONCAT(@var_year, '-', @var_month, '-01') + INTERVAL 4 MONTH AS date;
+------------+
| date |
+------------+
| 2015-03-01 |
+------------+
+1
source to share