The mistake is software which doesn't follow a recognized standard for date/time representation in its filenames. Ie, RFC 3339, ISO8601 or their union/intersection[1] (but preferably just ignore ISO8601 because its overcomplicated and RFC3339 is simpler and more intuitive).

In OP's examples, the filenames are YYYYMMDD_hhmmssssss, which is neither valid ISO8601 nor valid RFC 3999, as the former doesn't accept underscores (only 'T'), and the latter doesn't accept basic format dates (YYYYMMDD), only the equivalent of extended format (YYYY-MM-DD).

And if dates in file names simply used the extended format, the problem disappears. The lexical order is the natural order.

Alternatively, file managers that treat any digits as a number should be improved to recognize when a sequence of digits is not actually a number but a date/time, and order those chronologically. This might occasionally produce a few false positives, but I'd suspect it would be a rare occurrence.

[1]:https://ijmacd.github.io/rfc3339-iso8601/

If I want to sort by date, I sort by the "Date" column, not the file name

I hope you don't ever copy files.

I copy files all the time? I have files in my documents folder with creation dates in the 90's that have been copied forward between many computers.

Or edit any files with historical data.

Creation Date and Modification Date are separate

If I wanted to sort by date taken I would do just that using the EXIF data on them.