Can dates stored as string/text be compared directly?

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'

The reason you’re seeing lots of mentions of conversion when storing dates in a database, is that most database engines have a ‘native’ data type for date/time. SQLite doesn’t. It suggests you store dates as string or as julian date floating-point values.

Also, some other databases have rather obscure date-to-string functions (I’m looking at you, SQL Server), where it’s hard to figure out how to get a sortable date string. SQLite’s default date format (YYYY-MM-DD HH:NN:SS) is perfectly sortable and easy to read.

You’ll do fine with text dates when comparing or collating — as long as you use the right format, like you're presenting.

In fact, you can even do math, by using SQLite's date and time functions; they’re rather unorthodox but surprisingly flexible.

They can be compared as text as long as the actual values are what you expect them to be. That's the problem.

Comparing dates as text is like running with scissors. But SQLite makes you run with scissors all the time.

sqlite> create table test (d1 datetime primary key);sqlite> insert into test values ('2011-31-31 08:63:00');sqlite> select * from test;2011-31-31 08:63:00

A dbms that conforms to SQL standards will raise an "out of range" error when you try to insert or update values like '2011-31-31 08:63:00'. But SQLite lets you insert anything. You can even insert 'Catcall' into a datetime column. (Try it.)

SQLite gives you the same "running with scissors" behavior if you use text.

sqlite> create table test (d1 varchar(25) primary key);sqlite> insert into test values ('2011-31-31 08:63:00');sqlite> select * from test;2011-31-31 08:63:00

If values aren't what you expect them to be, you're likely to see the following kind of behavior.

sqlite> create table test (d1 varchar(25), d2 varchar(25));sqlite> insert into test values ('2011-01-01 08:00:00', '2011-01-01 08:15:00');sqlite> select d1<d2 from test;1sqlite> update test set d2 = '2011+01-01 08:15:00';sqlite> select d1<d2 from test;0