CODEWITHSUNDEEP
androidsqlsqlite
Kartik Domadiya
Kartik Domadiya

Reputation: 29968

SQLite Date Query In Android

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 ?.

Upvotes: 0

Views: 3190

Answers (2)

Sunil Kumar Sahoo
Sunil Kumar Sahoo

Reputation: 53647

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.

Upvotes: 3

Ted Hopp
Ted Hopp

Reputation: 234795

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.

Upvotes: 0

Related Questions