I can set the query just fine for posts that have the date stored in the database in Y-m-d format, but I have a custom post type where dates are stored as n/j/Y (month/day/year), and would like to sort the posts by this incorrectly formatted date. Is there a workaround?

Here is what I have tried, but it is just sorting by month, day, year, or just doesn’t sort with some of the other variations of meta_type and orderby:

function sort_admin_column($query) {
    // Get the post type from the query
    $post_type = $query->get('post_type');
            
    // Make sure we're not on front end and we're only looking at trainings
    if( !is_admin() || $post_type != 'trainings' )
        return;

    // Get the orderby qs
    $orderby = $query->get( 'orderby');

    // Check if we have event_date in the query string
    if ($orderby == 'event_date') {
        
        // Set the query
        $query->set('meta_key', 'event_date');
        $query->set('meta_type', 'DATE');
        $query->set('orderby', 'meta_value');
    }

    // Return it
    return $query;
}
add_action('pre_get_posts', 'sort_admin_column');

I have also tried several different things without success:

$query->set('meta_type', 'DATE');
$query->set('orderby', 'meta_value_num');

$query->set('meta_type', 'DATETIME');
$query->set('orderby', 'meta_value_num');

$query->set('meta_type', 'DATE');
$query->set('orderby', 'meta_value_date');

$query->set('meta_type', 'DATETIME');
$query->set('orderby', 'meta_value_datetime');