How do I order by date desc when the date is appended MYSQL?

I have a field called Trip ID in my system. This TRIP ID has a date appended to the last part of it. I would like to make my select field display the TRIP ID with the most recent date at the top aka order by date desc

although in my database I have an id column, I cannot order by Id desc because if i do this it will display the trip ID entered last which might be a trip id with a very very historic date.

How can I alter this code to achive what i want ?

 $resultSet = $mysqli->query("Select tripid from college order by date desc");

My DDL for Create Table

CREATE TABLE `college` (
  `id` int(11) NOT NULL,
  `lastentrytime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `thedate` datetime DEFAULT NULL,
  `ac` varchar(255) NOT NULL,
  `captain` varchar(255) NOT NULL,
  `firstofficer` varchar(255) NOT NULL,
  `examiner` varchar(255) NOT NULL,
  `safetyfirst` varchar(255) NOT NULL,
  `airport1` varchar(255) DEFAULT NULL,
  `airport2` varchar(255) NOT NULL,
  `bk1` datetime DEFAULT NULL,
  `bk2` datetime DEFAULT NULL,
  `airport3` varchar(255) NOT NULL,
  `airport4` varchar(255) NOT NULL,
  `bk3` datetime DEFAULT NULL,
  `bk4` datetime DEFAULT NULL,
  `airport5` varchar(255) NOT NULL,
  `airport6` varchar(255) NOT NULL,
  `bk5` datetime DEFAULT NULL,
  `bk6` datetime DEFAULT NULL,
  `airport7` varchar(255) NOT NULL,
  `airport8` varchar(255) NOT NULL,
  `bk7` datetime DEFAULT NULL,
  `bk8` datetime DEFAULT NULL,
  `airport9` varchar(255) NOT NULL,
  `airport10` varchar(255) NOT NULL,
  `bk9` datetime DEFAULT NULL,
  `bk10` datetime DEFAULT NULL,
  `airport11` varchar(255) NOT NULL,
  `airport12` varchar(255) NOT NULL,
  `bk11` datetime DEFAULT NULL,
  `bk12` datetime DEFAULT NULL,
  `airport13` varchar(255) NOT NULL,
  `airport14` varchar(255) NOT NULL,
  `bk13` datetime DEFAULT NULL,
  `bk14` datetime DEFAULT NULL,
  `airport15` varchar(255) NOT NULL,
  `airport16` varchar(255) NOT NULL,
  `bk15` datetime DEFAULT NULL,
  `bk16` datetime DEFAULT NULL,
  `Sectors` int(11) NOT NULL,
  `dutyon` datetime DEFAULT NULL,
  `dutyoff` datetime DEFAULT NULL,
  `flt_hrs` int(11) NOT NULL,
  `flt_min` int(11) NOT NULL,
  `duty_hrs` int(11) NOT NULL,
  `duty_min` int(11) NOT NULL,
  `tripid` varchar(255) NOT NULL,
  `dutyt` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

>Solution :

Assuming the the date is always in the same position and is consistent, try this

   SELECT tripid 
    FROM college 
    ORDER BY STR_TO_DATE(RIGHT(tripid, 10), '%Y-%m-%d') DESC