$query = $wpdb->prepare("SELECT post_id
FROM {$wpdb->prefix}postmeta t1
WHERE meta_key = 'expiry_date'
AND STR_TO_DATE(meta_value, '%Y-%m-%d') BETWEEN NOW() AND NOW() + INTERVAL 6 MONTH
AND EXISTS (SELECT 1 FROM wp_postmeta t2
            WHERE t2.meta_key = 'customer_email'
            AND t2.meta_value = %s
            AND t2.post_id = t1.post_id)
GROUP BY post_id", 'test@hotmail.co.uk');
$result = $wpdb->get_results($query, ARRAY_A);
$result returns empty because of this:
AND STR_TO_DATE(meta_value, '%Y-%m-%d') BETWEEN NOW() AND NOW() + INTERVAL 6 MONTH
meta_value column saves the date as a string hence I need to use STR_TO_DATE.
This query FULLY WORKS when I run it in phpmyadmin but not in my php wordpress file.
It only works inside my php file when I remove the line of code above.