Okay! So, having spent all the night in order to find a right solution... finally.. I did it. Here is the right method, that works in my situation.
The $wpbd method is pretty cool and complicated at the same time.
While using this method do not forget to cache queries properly with php-apc or memcache.
//Week events
$query_date = $_GET['date'];
$month_start = date('Ym01',strtotime('this month'));
$month_end = date('Ymt',strtotime('this month'));
//IF query Day is empty
if ( empty($query_date) ) {
//Custom query
$postids = $wpdb->get_col( $wpdb->prepare(
"
SELECT p.* FROM $wpdb->posts p
JOIN $wpdb->postmeta pm1 ON (p.ID = pm1.post_id)
JOIN $wpdb->postmeta pm2 ON (p.ID = pm2.post_id)
WHERE p.post_type = 'post'
AND p.post_status = 'publish'
AND ( ( pm1.meta_key = 'event_start_date'
AND CAST( pm1.meta_value AS DATE) BETWEEN '$month_start' AND '$month_end' )
OR ( pm2.meta_key = 'event_end_date'
AND CAST( pm2.meta_value AS DATE ) BETWEEN '$month_start' AND '$month_end' ) )
"
) );
//Insert Posts ID array and get their data
$args = array(
'post__in' => $postids,
'meta_key' => 'event_start_date',
'orderby' => 'meta_value_num',
'order' => 'ASC'
);
} else {
//Date
$query_date = date('Ymd', strtotime($query_date));
//Custom query
$postids = $wpdb->get_col( $wpdb->prepare(
"
SELECT p.* FROM $wpdb->posts p
JOIN $wpdb->postmeta pm1 ON (p.ID = pm1.post_id)
JOIN $wpdb->postmeta pm2 ON (p.ID = pm2.post_id)
WHERE p.post_type = 'post'
AND p.post_status = 'publish'
AND ( ( pm1.meta_key = 'event_start_date'
AND CAST( pm1.meta_value AS DATE) BETWEEN '$query_date' AND '$month_end' )
XOR ( pm2.meta_key = 'event_end_date'
AND CAST( pm2.meta_value AS DATE ) BETWEEN '$query_date' AND '$month_end' ) )
"
) );
//Insert Posts ID array and get their data
$args = array(
'post__in' => $postids,
'meta_key' => 'event_start_date',
'orderby' => 'meta_value_num',
'order' => 'ASC'
);
}
$wp_query = new WP_Query( $args );
//End of custom query
