CODEWITHSUNDEEP
sqlsqlite
user1592380
user1592380

Reputation: 36367

Selecting records between 2 dates with sqllite

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

Answers (2)

dan04
dan04

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

David דודו Markovitz
David דודו Markovitz

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

Related Questions