Using sqlite strftime function in android

Question

Hello, I have entered some dates in my database.

When I use sqlite3 in YYY-MM-DD format in another table, when I make my query, for example

 SELECT * FROM SDay WHERE strftime('%Y-%m-%d', date)>=strftime('%Y-%m-%d','2013-02-21') AND strftime('%Y-%m-%d', date)<strftime('%Y-%m-%d','2013-09-15') 

It works great.

On the other hand, when I use the YYY-MM format in another table, then

 SELECT * FROM SMonth WHERE strftime('%Y-%m', date)>=strftime('%Y-%m','2013-02') AND strftime('%Y-%m', date)<strftime('%Y-%m','2013-09') 

will not work. Why is this happening and how can I fix it?

Thanks.

Answer 1

Your second strftime statement provides an invalid timestring . This forces the function to return NULL :

 sqlite> .nullvalue <NULL> sqlite> select strftime('%Y-%m','2013-02'); <NULL> 

Just add the day to the date you provide and it will work correctly:

 sqlite> select strftime('%Y-%m','2013-02-01'); 2013-02 

You can find a list of valid timestring formats here .