[Resolved] Filter posts by Date field with options just like for Post Date filter

[Author]

Posts

[navneetR]

I am trying to: Filter posts by a Date Field. I see there are powerful options available when filtering by Post Date (https://toolset.com/documentation/user-guides/filtering-views-query-by-date/.

How can I use the same options (as shown in this image: hidden link) for Date fields created within Types? Filtering by date fields based on current month, year etc. vs creating shortcodes would be great.

[navneetR]

Any help?

Additional info: I am trying to filter results in two views using 2 different date fields.

(1) First view: One field is a "Birthday" date field and the first view should show if there are any employees with birthdays in the current month.

(2) Second view: There is another date field ("hire date") and the view should (a) filter employees and only show the ones that have anniversary in the current month, AND (b) show the number of the anniversary years for employees returned (current year minus hire year).

I'm really stuck with the filtering part on above scenarios before the query results are generated in the view loop output. I can create a shortcode for (2b) but I need a clean way to filter the results first. I tried using wpv_filter_query and wpv_filter_query_post_process but don't have a solution yet.
If there was a way to filter the dates for custom fields like there is for Post Date things would be much easier.

[Minesh Supporter Languages: English (English ) Timezone: Asia/Kolkata (GMT+05:30)]

Hello. Thank you for contacting the Toolset support.

As per our support policy, we will handle only one issue per ticket and I'll handle your first view query #1 here. Please kindly open a new ticket with your each new question. This will help other users searching on the forum.

First view: One field is a "Birthday" date field and the first view should show if there are any employees with birthdays in the current month.
So - lets start:
=> To filter the view showing current month posts irrespective of day and year, please try the following steps:

1)
Create your view and Add following code to your current theme's functions.php file:

add_shortcode( 'get_view_results', 'func_get_view_results');
function func_get_view_results() {
	global $wpdb;
	
		$sql = "SELECT post_id FROM ".$wpdb->prefix."postmeta,".$wpdb->prefix."posts  
                WHERE (".$wpdb->prefix."postmeta.post_id = ".$wpdb->prefix."posts.ID)
                AND ".$wpdb->prefix."postmeta.meta_key = 'wpcf-birthdate' 
				AND month(FROM_UNIXTIME(".$wpdb->prefix."postmeta.meta_value)) = month(now())
				AND ".$wpdb->prefix."posts.post_type = 'service' 
				AND ((".$wpdb->prefix."posts.post_status = 'publish' OR ".$wpdb->prefix."posts.post_status = 'private'))";
		$res = $wpdb->get_results($sql);
		
       
    	
	if(empty($res)){
		return 0;
	}else{
		return 1;
	}
	
}

Where:
- Replace 'service' with your post type slug
- Replace 'birthdate' with your field name

add_filter( 'posts_where' , 'func_posts_where');
function func_posts_where( $where ) {
	global $WP_Views;
	global $wpdb;

	if($WP_Views->current_view == 9999){
		
		   $where .= " And ".$wpdb->prefix."posts.ID IN  (SELECT post_id FROM ".$wpdb->prefix."postmeta
                                                WHERE (".$wpdb->prefix."postmeta.post_id = ".$wpdb->prefix."posts.ID)
                                                AND ".$wpdb->prefix."postmeta.meta_key = 'wpcf-birthdate' 
												AND month(FROM_UNIXTIME(".$wpdb->prefix."postmeta.meta_value)) = month(now())
                                            )";
		 
 
	}
	return $where;
}

Where:
Replace '9999' with your original view ID

2)
Now, you can dispaly your view conditionally as given under - if there is birthdays in current month it will dispaly the view otherwise not:

[wpv-conditional if="( '[get_view_results]' eq '1' )"]

[wpv-view name="your-view-name"]

[/wpv-conditional]

Important:
Please dont forget to register the "get_view_results" shortcode at:
=> Toolset => Settings => Front end content => Third-party shortcode arguments

I hope this solution will help you to resolve your issue.