Can dates stored as string/text be compared directly?

Question

I'm working in SQLite so I'm storing dates as just text.

YYYY-MM-DD hh:mm:ss

As far as I can determine, I should be able to order them or compare them with each other just as they are not worrying about date functions since any incrementing starts right and moves left, just like numbers, and all the values are numeric, and the non-numeric characters are always standardized and I'm using 24-hour time.

I keep seeing people online mentioning how dates as text have to be converted for any comparisons, but I don't see why they wouldn't just work as-is so long as they're in that greatest -> smallest order. Obviously I can't do math, but Select DateTime From Table where DateTime > 2010-04-21 15:34:55 should be totally reliable, right?

Is there some exception I'm not thinking of?

Answer 1

This works just fine. SQLite does not have an actual DATE (or DATETIME) data type like many other databases do. Your options are to store the date in a string format that will sort properly (YYYY-MM-DD or YYYY-MM-DD hh:mm:ss) or to convert the date to a number and store that instead.

If you use the string format, then you have to include quotes of course:

SELECT DateTime FROM Table WHERE DateTime > '2010-04-21 15:34:55'