wp_query, calculate with two dates when ‘key’ is text format

The meta_value is a longtext type column and isn’t technically a date, wordpress only cast the value based on the type you pass. and Its not about being a text format, its because of your date format that isn’t supported by mysql. your resulting query will be CAST('02.05.2024' AS DATE) which mysql transform it to null because of unsupported date format.

You have two options;

1. Save the date in a format YYYY-MM-DD which mysql can CAST and can do date related calculation like BETWEEN

2. Use $wpdb global variable and perform raw SQL query using function STR_TO_DATE on that date meta value

e.g.

SELECT *
FROM wp_posts as post

LEFT JOIN wp_postmeta as pm
    ON  post.ID = pm.post_id
    AND pm.meta_key = 'members_entry'
    
WHERE STR_TO_DATE(pm.meta_value, '%m.%d.%Y') BETWEEN '2024-02-05' AND '2024-02-06'
AND post.post_type="staff_cpt"
AND post.post_status="publish"
ORDER BY post.post_title ASC;