Retrieve date wise data from database in Android

Question

In my database I have 1 table with date as one of the column, which stores the date when user add some data in my table.

Now I want to retrieve the data for the current month alone. I searched through Google but didn't get appropriate answer.

My table creation code:

"create table incomexpense(
    _id integer primary key autoincrement,
    "+"price text not null,description text not null,
    "+"quantity text not null,
    "+"total text not null,
    "+"category text not null,
    "+"recurrence text not null,
    "+"date text not null,
    "+"groups text not null,
    "+" recurrenceid text not null);";

Can anyone please help how to get the date of 7th day form the current date.

What format is the the date in? Without knowing that, it's almost impossible to give any recommendations. — Barak
– Barak, Commented Jul 24, 2012 at 12:12
Barak..date format is "dd/MM/yyyy"..Also i got 1 answer: select column_name from table_name where strftime('%Y-%m', column_name) = '2011-05';In this they r giving static value for date'2011-05'.But i want is it should load automatically the current year and month.. — prakash .k
– prakash .k, Commented Jul 24, 2012 at 12:14

Sathyajith Bhat · Accepted Answer · 2012-07-24 20:21:01Z

7

If you want to "automatically" load the current year/month, use SQLite's strftime function with 'now' modifier & '%Y-%m' format mask.

select strftime('%Y-%m', 'now');

Now, coming to your question:

Now I want to retrieve the data for the current month alone.

Well, here comes a mess. You can't store the date as a datetime type, instead it's stored as a string in a format SQLite can't parse using date functions. So you'll have to convert it into a format which SQLite can understand.

You can do that by using the usual substr function

substr(exp_date, 7) || '-' || substr(exp_date, 4,2) || '-' || substr(exp_date, 1,2)

So this will covert dd/mm/yyyy to YYYY-mm-dd format.

So the full query will be:

SELECT *
FROM   incomexpense
WHERE  Strftime('%Y-%m', 'now') = Strftime('%Y-%m', Substr(exp_date, 7)
                                                    || '-'
                                                    || Substr(exp_date, 4, 2)
                                                    || '-'
                                                    || Substr(exp_date, 1, 2))

See how this works over here.

answered Jul 24, 2012 at 20:21

Sathyajith Bhat

22k22 gold badges102 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

prakash .k Over a year ago

Thanks Sathya..Can anyone please tell me..how can i get the date of 7days from the current date..

Sathyajith Bhat Over a year ago

@prakash.k -> select date('now','+7 days')

Dattatray · Accepted Answer · 2012-07-24 12:18:52Z

0

Android doesn't have a bulit in data type as "Date". You can either store it as string and parse it appropriately when you use it. So with this option, unless you retrive and parse the Date , you won't be able to find the records in current month.

Other option is you store it as number - Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

With some calculation in the where clause you can find out the records for the current month.

answered Jul 24, 2012 at 12:18

Dattatray

1,8752 gold badges27 silver badges56 bronze badges

Collectives™ on Stack Overflow

Retrieve date wise data from database in Android

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related