SELECT
`s`.show_id,
STR_TO_DATE( `s`.start_date, '%m/%d/%Y' ) AS start_date,
STR_TO_DATE( `s`.end_date, '%m/%d/%Y' ) AS end_date,
UPPER(DATE_FORMAT(STR_TO_DATE( `s`.start_date, '%m/%d/%Y' ), '%M %d %Y')) AS start_day,
UPPER(DATE_FORMAT(STR_TO_DATE( `s`.end_date, '%m/%d/%Y' ), '%M %d, %Y')) AS end_day,
UPPER(DATE_FORMAT(STR_TO_DATE( `s`.start_date, '%m/%d/%Y' ), '%d')) AS day,
UPPER(DATE_FORMAT(STR_TO_DATE( `s`.start_date, '%m/%d/%Y' ), '%b')) AS month,
`s`.title,
`s`.post_header,
`s`.short_description,
`s`.description,
`s`.status,
`s`.single_ticket,
`s`.subscribe_show_listing,
`s`.p_meta_keywords,
`s`.p_meta_desc,
`s`.back_image_credit,
`s`.is_free,
`s`.heading_picture,
`s`.heading_picture_alt,
`s`.background_picture,
`s`.background_image,
`s`.list_image,
`s`.short_title,
`s`.show_seo,
`s`.event_type,
`s`.running_time,
`s`.group_event,
`s`.ticket_link,
`s`.related_show,
`s`.quote,
`short_cuts`.`source` AS `short_cuts`,
`venue`.*,
events.e_time,
events.e_end_time
FROM `shows` `s`
LEFT JOIN `venue` ON `venue`.`v_id`=`s`.`show_venue`
LEFT JOIN `events` ON `events`.`e_show_id`=`s`.`show_id`
LEFT JOIN short_cuts ON short_cuts.destination = CONCAT('event-detail.php?id=', `s`.`show_id`)
WHERE
1=1
AND STR_TO_DATE( `s`.`end_date`, '%m/%d/%Y' )>= CURDATE()
AND (YEAR(STR_TO_DATE(`s`.`start_date`,'%m/%d/%Y')) >= YEAR(CURDATE()) OR
YEAR(STR_TO_DATE(`s`.`end_date`,'%m/%d/%Y')) >= YEAR(CURDATE()) )
AND
`s`.status=1
AND STR_TO_DATE(events.e_date, '%m/%d/%Y') > DATE_SUB(NOW(),INTERVAL 2 DAY) AND `s`.`show_type`='2'
GROUP By `s`.`show_id`
ORDER BY STR_TO_DATE( `s`.`end_date`, '%m/%d/%Y' ) ASC
