I have the custom field "members_entry" which is the key and it is in text format (not in date format!). I cannot change the format of the custom field in WP, it has to be text.

I have the following code:

$newMember = array(
'post_type' => 'staff_cpt',
'post_status' => 'publish',
'posts_per_page' => -1,
'orderby' => 'title',
'order' => 'asc',
'meta_query' => array(
    array(
        'key' => 'members_entry',
        'value' => array($entry, '02.05.2024'),
        'compare' => 'BETWEEN',
        'type' => 'DATE'
    )
 )                    
 );

I need to display all members entries till date 02.05.2024 (e.g.). This does not work because the key (members_entry) is not in date format.

Thanks for helping!

I have the custom field "members_entry" which is the key and it is in text format (not in date format!). I cannot change the format of the custom field in WP, it has to be text.

I have the following code:

$newMember = array(
'post_type' => 'staff_cpt',
'post_status' => 'publish',
'posts_per_page' => -1,
'orderby' => 'title',
'order' => 'asc',
'meta_query' => array(
    array(
        'key' => 'members_entry',
        'value' => array($entry, '02.05.2024'),
        'compare' => 'BETWEEN',
        'type' => 'DATE'
    )
 )                    
 );

I need to display all members entries till date 02.05.2024 (e.g.). This does not work because the key (members_entry) is not in date format.

Thanks for helping!

• wp-query
• array

• why can't it be a date format? Please edit your question to add context for why this is the case and where this value comes from, it will help avoid unnecessary questions and explain your situation better. Also please fix the indentation of the code – Tom J Nowell ♦ Commented Apr 14, 2023 at 15:54

1 Answer 1

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;