Sqlite Full Text Search Query Puzzle -

i have data in format yyyy-mm-dd hh:mm:ss. stored text.

i want query matches given day(yyyy-mm-dd).

e.g

select * test test match '2014-03-30*' 

when try that, returns data excluding of march 2014.

if try

select * test test match '2014-04-30*' 

it returns data excluding of april 2014.

i puzzled!...i getting opposite of want!

any reason strange behavior?

this full code....testing date pattern

public list <transactions> gettransactionsvirtual(string token)     { list<transactions> trans = new arraylist<transactions>(); sqlitedatabase db;   string sql= " select "  + message + "," + tdate +  ","+ service_provider +             "  " + table_name_virtual + " "+ table_name_virtual + " match " +  "?" +             " order " + tdate + " desc"; //check entered string...if date string strip .. string pattern="^(19|20)\\d\\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$"; string tokenedit=null; string newtoken=null; if (token.matches(pattern)) {     tokenedit= token.replace("-", " ");     log.e("testtoken", tokenedit);      newtoken =  tokenedit+"*" ; }else {      newtoken= token+ "*"; }    string [] args= new string[]{newtoken}; log.e("sqlamatch", sql);  db= this.getreadabledatabase();    cursor c = db.rawquery(sql, args);     if(c.movetofirst())    {        do{            transactions t=new transactions();             t.settransactiondate(c.getstring(c.getcolumnindex(tdate)));             t.setmessage(c.getstring(c.getcolumnindex(message)));                 t.setserviceprovider(c.getstring(c.getcolumnindex(service_provider)));             //log.e("msg",t.getmessage().tostring());             trans.add(t);        }while(c.movetonext());    }    return trans;     } 

the full-text search mechanism designed searching words inside texts.

with default tokenizer, 3 fields of date (yyyy, mm, dd) parsed single, independent words, , delimiters ignored.

an fts query 2014-03-30* searches documents (=records) that

• contain word 2014, and
• do not contain word 03, and
• do not contain word beginning 30.

you need phrase search:

select * test test match '"2014 03 30"' 

if data has fixed format, should not use fts table in first place.