Reputation: 36367
I have some data in a sqllite db of the form:
id column1 date
111 280 1/1/2014
112 281 invalid invalid invalid
113 282 invalid invalid invalid
114 275 1/2/2014
......................
338 273 1/31/2014
I want to select all the records in January. the date column has the form mm/dd/yyyy . The Kicker is that there are some intervening records that are invalid and don't have a date. I want to select these as well, so that I end up with all the records between id 111-338
All the columns except for id are in text format.
How can I do this?
Upvotes: 0
Views: 65
Reputation: 91227
the date column has the form mm/dd/yyyy
This is your problem. You should be using yyyy-mm-dd format, which sorts in lexicographic order and is compatible with SQLite date and time functions.
Upvotes: 1
Reputation: 44991
Not bulletproof but worth a try.
select *
from t
where cast (substr(date,1,instr(date,'/')-1) as int) in (0,1)
;
Upvotes: 1