The SQL that returns the most recent modified date across all your articles:
SELECT `modified` FROM `#__content` GROUP BY `modified` ORDER BY `modified` DESC LIMIT 1
Then you would need to build a Joomla select query in PHP:
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('modified')));
$query->from($db->quoteName('#__content'));
$query->group('modified');
$query->order('modified DESC');
$db->setQuery($query);
$mostrecent = $db->loadResult();
echo $mostrecent;
You would then further convert the result into a date (as the result is returned as a string) and then adjust how your most recent date is formatted:
$thedate = date_create($mostrecent);
echo '<em>Site content last modified on '. date_format($thedate,'l jS F Y').'<em>';
Which at the time of my reply would return:
Site content last modified on Saturday 11th December 2021