CODEWITHSUNDEEP
mysqlselectwhere-clause
Hanner
Hanner

Reputation: 371

How to get all records before any specified month in mysql

I want to select all records before any specified month using mysql. Here is my attempted statement:

SELECT SUM(amount) as allPreviousAmount FROM `fn_table`
WHERE MONTH(transdate) < 1 AND YEAR(transdate) = YEAR(CURRENT_DATE())

transdate is datetime data type.

I have data on December 2018. But this does not select the data. Then I remove the Year part, still no data is selected. The transdate is 2018-12-31 15:59:41.

Please fix it and explain why this is not working.

Upvotes: 1

Views: 483

Answers (2)

P.Salmon
P.Salmon

Reputation: 17590

Multiply year by 100 add month (on both sides) and compare.

set @dt1 = '2019-10-01';

select  @dt1,current_date,
            year(@dt1) * 100 + month(@dt1),
            case 
                when year(@dt1) * 100 + month(@dt1) < year(current_date) * 100 + month(current_date) then
                    'Less than'
                else 'other'
                end as result;

------------+--------------+--------------------------------+-----------+
| @dt1       | current_date | year(@dt1) * 100 + month(@dt1) | result    |
+------------+--------------+--------------------------------+-----------+
| 2019-10-01 | 2019-11-14   |                         201910 | Less than |
+------------+--------------+--------------------------------+-----------+
1 row in set (0.00 sec)

Upvotes: 2

forpas
forpas

Reputation: 164064

This will do (assuming there are no future dates):

SELECT SUM(amount) as allPreviousAmount 
FROM `fn_table`
WHERE MONTH(transdate) < ? OR YEAR(transdate) < YEAR(CURRENT_DATE())

Replace ? with the month that you want the results for.

Upvotes: 2

Related Questions