meta query based on month regardless of year

While the regexp solution will function, it quickly gets slower as the number of users increases. This is because the database cannot perform optimisation and indexing tricks, and forces a full table scan. The more users, and the more meta, the greater the cost of the query. At a minimum it requires caching to avoid a worst case scenario.

This is true of all meta, be it post meta, comment meta, term meta or user meta. Searching for things by the contents of their meta is super expensive.

But it can be avoided.

As well as storing the data in a user meta key/value pair, register a private/non-public custom taxonomy. When the user saves their profile, set a term on that user in this taxonomy using the month as a value.

Then, you can use an ultra fast query to get users with that month.

First, register the private user taxonomy:

function hidden_user_taxonomies() {
    register_taxonomy( 'birthday_months', 'user', [
        'label'        => __( 'Birthday Months', 'textdomain' ),
        'public'       => false
        'hierarchical' => false,
    ] );
}
add_action( 'init', 'hidden_user_taxonomies', 0 );

Notice the object type here is not a post type, but user, and that public is set to false.

Then, we hook into user profile save, and set the term for that user:

add_action( 'personal_options_update', 'save_hidden_birthday_month' );
add_action( 'edit_user_profile_update', 'save_hidden_birthday_month' );

function save_hidden_birthday_month( $user_id ) {
    $birthday = get_field( 'birthday field name' );
    $month = ... get the birthday month out of the string....
    wp_set_object_terms( $user_id, [ strval( $month ) ], 'birthday_months', false );
}

Now we have a hidden user taxonomy that contains terms for each month of the year, and users are assigned to those months when their profile is updated. Also notice the intval call, this is so we store 4 rather than 04.

So how do we fetch the users? Simples:

function users_in_birthday_month( $month ) : array {
    // fetch the month term
    $month = get_term_by( 'name', $month, 'birthday_months' ); // who has birthdays in July?
    if ( !$month ) {
        // nobody has a month in July as there is no term for July yet
        return [];
    }
    // fetch the user IDs for that month
    $user_ids = get_objects_in_term( $month->term_id, 'birthday_months' );
    if ( empty( $user_ids ) ) {
        // there were no users in that month :(
        return [];
    }
    $user_query = new WP_User_Query( [
        'include' => $user_ids,
    ] );
    return $user_query->get_results();
}

So to get all users with a birthday in July?

$july_birthdays = users_in_birthday_month( 7 );
foreach ( $july_birthdays as $user ) {
    // output user display name
}

And if you want to display the full birthday, just fetch the original post meta field.

This means:

• You can now use ultra fast taxonomy queries!
• Your query will not get exponentially slower as your userbase grows
• This data is very cacheable, and will see more performance gains if an object cache is in use
• There are only 12 possible queries to cache!
• You could extend this to include days of the month, years, etc
• It may be necessary to fetch the month value from $_POST rather than get_field as ACF may not have saved the value yet

Remember, don't use meta to search for things, that's what terms and taxonomies were created for.

Posts aren't the only things that can have taxonomies and meta, for more information on user taxonomies, see this helpful article from Justin Tadlock.

If that field is definitely stored in a string like: 20000101 then you can use the REGEXP compare method to do what you want.

The code below uses the WP_Query meta fields to make a regex which takes any character for the year and day, and matches a particular month.

You'd need to give this a go on your data, but it would look something like this:

$monthNumber = 6; // e.g. June
// ensure always two characters, so June will become "06"
$monthNumberString = str_pad($monthNumber, 2, "0", STR_PAD_LEFT); 
// regex to only match month
$matchMonthRegex = '....' . $monthNumberString . '..'; 

$args = array(
    'meta_key' => 'YOUR_BIRTHDAY_META_FIELD_NAME_HERE',
    'meta_value' => $matchMonthRegex,
    'meta_compare' => 'REGEXP'
);

$result = WP_Query($args);

Let me know if that works?