[SOLVED] WP Query to identify future dated Woocommerce products with custom meta date field

WP Query to identify future dated Woocommerce products with custom meta date field

I am trying to search for future dated Woocommerce products, but the query is not working. As it happens, the _custom_date field values are stored in UK date format e.g. 24/01/2025. Any ideas?

$args = array(
    'post_type' => 'product',
    'posts_per_page' => 500, 
    'meta_query' => array(
        array(
            'key' => '_custom_date',
            'value' => date("d-m-Y"),
            'compare' => '>=',
            'type' => 'DATETIME',
        ),
    ),
    'order'     => 'ASC',
    );

Solution

WordPress does not recognize d/m/Y as a valid DATETIME format, and it sorts meta values as strings instead. You need to store and query the date in YYYY-MM-DD format.

update_post_meta($product_id, '_custom_date', date('Y-m-d', strtotime($input_date)));

Then, you can use:

'value'   => date("Y-m-d"),
'type'    => 'DATE',

If you don't want to make this change and want to continue with your current query then :

your _custom_date field is stored in d/m/Y, you need to:

Convert the current date to d/m/Y format before comparing.
Use 'type' => 'CHAR' instead of 'DATETIME' because the stored format is not MySQL-friendly. So the query will look like :

$args = array(
    'post_type'      => 'product',
    'posts_per_page' => 500, 
    'meta_query'     => array(
        array(
            'key'     => '_custom_date',
            'value'   => date("d/m/Y"), // Convert current date to d/m/Y format
            'compare' => '>=',
            'type'    => 'CHAR', // Because _custom_date is stored as a string
        ),
    ),
    'orderby'   => 'meta_value',
    'order'     => 'ASC',
);