You have to make everything the same type, either numeric or text. When possible though you want to do your conversions on your input constant(s), and not on the stored values. That way you can use an index on the stored value.
sqlite> create index date_type_index on date_types(date_type); sqlite> explain query plan ...> select * from date_types ...> where strftime('%s', '2018-01-01') <= date_type ...> and date_type < strftime('s', '2018-01-01', '+1 year'); QUERY PLAN `--SEARCH TABLE date_types USING COVERING INDEX date_type_index (date_type>? AND date_type<?) sqlite> explain query plan SELECT "id", "date_type" FROM "date_types" WHERE "date_type" LIKE '2018-%-%'; QUERY PLAN `--SCAN TABLE date_types sqlite> explain query plan SELECT id, date_type FROM date_types WHERE date(date_type, 'unixepoch') LIKE '2018-%-%'; QUERY PLAN `--SCAN TABLE date_types -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of dmp Sent: Thursday, July 05, 2018 2:51 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Date Search Given: SELECT STRFTIME('%s', '2018-01-01'); 1514764800 SELECT STRFTIME('%s', '2017-01-01'); 1483228800 CREATE TABLE date_types( id INTEGER PRIMARY KEY, date_type DATE ); INSERT INTO "date_types" ("id", "date_type") VALUES(1, 1514764800); INSERT INTO "date_types" ("id", "date_type") VALUES(2, 1483228800); date_type: Affinity now Integer. This does not seem to be possible, without some type of conversion to a Integer(s) value(s) then searching in a numeric range for the given TEXT date? SELECT "id", "date_type" FROM "date_types" WHERE "date_type" LIKE '2018-%-%'; danap. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users