0

I've modified this post a few times, asking for help. And I finally got it figured out, so I thought I would share what worked for me.

The goal was to create a Meta Query in Wordpress on a Custom Date Field, in a custom post type. Not the WP Core post date. At first I wanted to display the month and the year date with previous and next buttons that would advance to the next month. But I gave up on that and thought it would work to query months and display them separately, possibly using html accordion tabs.

Thanks to @Sim1-81 using AND (wpostmeta.meta_value BETWEEN '2020-03-01 00:00:01' AND '2020-03-31 23:59:59') - worked, for March of 2020. But I needed the year to be based on the "current year." Pulling the month and date this way was fine, but I wanted the year to change so it pulled data from 1. March of current year - or, 2. March of the following year, if it had passed (April, etc).

This code does exactly that:

if($n>3){
$y = date('Y') + 1;
} else {
$y = date('Y');
}

$querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wpostmeta.meta_key = 'custom_date_field'
AND (wpostmeta.meta_value BETWEEN '".$y."-03-01 00:00:01' AND '".$y."-03-31 23:59:59')
AND wposts.post_status = 'publish'
AND wposts.post_type = 'event'
ORDER BY wpostmeta.meta_value DESC
";
  $pageposts = $wpdb->get_results($querystr, OBJECT_K);

 ?>```
MARWEL58
  • 19
  • 1
  • 5
  • maybe duplicate of https://stackoverflow.com/questions/29275403/get-posts-published-between-specific-dates-in-wordpress – Sim1-81 Sep 09 '19 at 14:59
  • it looks like the linked post is querying the WP post date. I need to query a custom date field, from a table in the database. The meta query in the code above works great, but I don't know how to change ````AND wpostmeta.meta_value >= CURDATE()```` to show the current month, etc. – MARWEL58 Sep 09 '19 at 15:07
  • change the query to introduce both dates `AND (wpostmeta.meta_value BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')` – Sim1-81 Sep 09 '19 at 15:27
  • thanks. I'll see if that gets me somewhere - It would be ideal if went past a year. – MARWEL58 Sep 09 '19 at 15:30
  • I was curious about using BETWEEN in this string, so I tried it with the beginning and end of the month of September, 2019 - since I was already getting the year with CURDATE(). But it threw a syntax error. – MARWEL58 Sep 09 '19 at 16:44
  • post your query string please – Sim1-81 Sep 09 '19 at 20:09
  • Ok. Sorry, I tried it again and the September 2019 events will display. So your between string could work, if I could get the correct variables in place, rather than hard-coded dates. THANK YOU! – MARWEL58 Sep 09 '19 at 21:00
  • try to write dates converting to date string format via MySql with STR_TO_DATE('2010-09-29 10:15:55','Y-m-d H:i:s') for both start and end date. ref: [w3schools.com - func_mysql_str_to_date](https://www.w3schools.com/sql/func_mysql_str_to_date.asp). You can also use twice statement `WHERE ....... AND wpostmeta.meta_value >= STR_TO_DATE('2010-09-01 10:15:55','Y-m-d H:i:s') AND wpostmeta.meta_value <= STR_TO_DATE('2010-09-29 10:15:55','Y-m-d H:i:s')` – Sim1-81 Sep 10 '19 at 08:34
  • I didn't give up on this project, but I had to take a break on it. I still need to solve the problem of BETWEEN hard coding a specific year. I may be approaching this all wrong. But I'm hoping to get the months to sort, no matter the year. – MARWEL58 Sep 22 '19 at 19:45

0 Answers0