How to filter month by string query

Question

I have a SQL like this in my apex code:

public static list<FB_Employees__c> getRecords() {
         return [
                SELECT Name, Date_of_birth__c
                FROM FB_Employees__c
                WHERE CALENDAR_MONTH(Date_of_birth__c) = 7
                ];
        }

It will filter all the users who have birthday on July, by doing this way, I will have to change the code every month and I think I should have a another way to do it automatically, I think of a way using string query but I'm not still familiar with it? Can anyone suggest me an idea to improve the code?

score 1 · Answer 1 · answered Jul 15 '21 at 14:16

1

Change it to look like this:

WHERE Date_of_birth__c = THIS_MONTH

THIS_MONTH allows us to easily compare to the current month.

answered Jul 15 '21 at 14:16

StardustGogeta

3,331
2
18
32

Well, for a reason I can’t use THIS_MONTH when I query, it will not show up anything. That’s the reason why I need you guys help – Lê Quang Anh Jul 15 '21 at 14:30

score 0 · Answer 2 · answered Jul 15 '21 at 14:18

0

Use CALENDAR_MONTH(Date_of_birth__c) = THIS_MONTH, this will automatically compare DOB with current month

answered Jul 15 '21 at 14:18

Tejas

391
3
11

Well, for a reason I can’t use THIS_MONTH when I query, it will not show up anything. That’s the reason why I need you guys help – Lê Quang Anh Jul 15 '21 at 14:30

score 0 · Answer 3 · answered Jul 15 '21 at 17:38

Date class has a perfect method for your case: month()

Returns the month component of a Date (1=Jan).

So your APEX method should be:

public static list<FB_Employees__c> getRecords() {
    Integer thisMonth = System.today().month();
    return [
        SELECT Name, Date_of_birth__c
        FROM FB_Employees__c
        WHERE CALENDAR_MONTH(Date_of_birth__c) = :thisMonth
    ];
}

Trunk · Accepted Answer · 2021-07-15T20:38:02.713

You just want to filter those employees whose birthdays are in the present month ?

First get the month as an integer from the present date.

Then insert this value in your query using single quotes ('....') around your month value and treating the whole query as a single string.

        public static list<FB_Employees__c> getRecords() 
        {
           int thisMonth = Calendar.getInstance().get(Calendar.MONTH) + 1;     // Since Java month range is 0..11
           return [
                "SELECT Name, Date_of_birth__c
                FROM FB_Employees__c
                WHERE CALENDAR_MONTH(Date_of_birth__c) = '" +  thisMonth + "';"
                ];
        }

How to filter month by string query

4 Answers4