SQLite Date Query In Android

Question

I am using SQLite database for my application.

The Table Structure Goes Like :

_id  : integer primary key
name : text
day  : date

I am able to store date in format : dd-mmmmm-yyyy eg. 15-June-2011

But when i tried to retrieve all records filtered by date from the database it returns me null.

database.query(DATABASE_TABLE, new String[] { "strftime('%d-%mm-%Y',date('now'))","strftime('%d-%m-%Y',"+KEY_DAY+")" },
"strftime('%d-%m-%Y',date('now'))=" + KEY_DAY , null,null,null,null,null);

It didnt match with anyrow's date even though there were some matching dates.

I have already gone thru documentation of SQLite. But didn find any solution yet.

I want to have something like :

 select * from table where day=curdate();

How can i do the same task in SQLite ?. (Yes I am flexible to change the format of date stored in Dateabase)

What are other alternatives for the same task ?.

http://stackoverflow.com/questions/4013508/andorid-sqlite-date-comparation did you try this? — Pankaj Kumar, Jun 15 '11 at 05:38
I do not think that strftime is a built-in SQLite function either. — Philip Sheard, Jun 15 '11 at 05:40
@Philip Sheard : i saw that in SQLite documentation. http://www.sqlite.org/lang_datefunc.html — Kartik Domadiya, Jun 15 '11 at 05:41

score 3 · Accepted Answer · answered Jun 15 '11 at 06:07

In java programming you can convert any date format into long (time in milliseconds) and viceversa. My opinion is while storing format the date into long format in java and then store long value of date in database. also while retrieving you can retrieve the long value and then format that as per your expected date format. I have been using this type of logic for several application.

Thanks Deepak.

score 0 · Answer 2 · answered Jun 15 '11 at 05:36

0

The function strftime('%d-%m-%Y',date('now')) returns a string with the month in numeric format (from 01 to 12). As far as I can tell from the docs, there is no format specifier to return the full name of the month.

I think you'll have to store your dates using numerical month specifiers instead of names.

answered Jun 15 '11 at 05:36

Ted Hopp

232,168
48
399
521

So i have to change the date format also while storing it in database?.. If yes which format is best in that ? – Kartik Domadiya Jun 15 '11 at 05:39
@Kartik - The options are described in the docs on data types. I would go with ISO-format TEXT or Julian date REAL, if only because these don't have the limitations of Unix time (which overflows 32 bits in a couple of decades and has problems before 1/1/70). – Ted Hopp Jun 15 '11 at 06:12

SQLite Date Query In Android

2 Answers2