Comparing SQL dates in SQL Standard format. Does it work always?

I was looking for some answers on comparing dates in SQLite, and some answers pointed that as long as your date format is in:

YYYY-MM-DD HH:MM:SS (assuming HH:MM:SS is in 24-hour format)

you'll be ALWAYS fine if you compare them as strings (either comparing one date against other, or checking if a date is between 2 dates). Something like:

SELECT * FROM table WHERE 'YYYY-MM-DD' BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'

One thing I noticed for this format is that it starts with the highest possible "range", year, followed by month, etc. I'm trying to grasp on whether this will work always (algos are not my strong side), so my question is, are there any "edge" cases against which this will NOT work?