Select data from current month in android with SQLite [duplicate]

Question

In my App I am trying to show balance for current month but I can't accomplish that.

This is my code

// Count Current Month Income
public float CurrentMonthIncome(){
    float x = 0;
    SQLiteDatabase db = getReadableDatabase();
    String getamountdata = "SELECT SUM(inc_amount) AS totalInc FROM "+ TABLE_ENTRY + " where year(entry_date)= year(CURDATE()) and MONTH(entry_date)=MONTH(CURDATE())";
    Cursor c = db.rawQuery(getamountdata, null);
    if(c.moveToFirst()){
        x = c.getFloat(0);
    }
    return x;

}

When I execute that code, I get an exception like

E/SQLiteLog: (1) no such function: CURDATE
Shutting down VM
FATAL EXCEPTION: main

Answer 1

According to this link use date('now') instead of curdate().

In order to take current date, in MySQL and SQL Server, you can use CURDATE() but in SQLite, you don't have this option. To get current date, you have to use date('now') in your query to get the current date in that specific device.

So your query will be like this:

SELECT SUM(inc_amount) AS totalInc 
FROM "+ TABLE_ENTRY + " WHERE strftime('%Y',entry_date) = strftime('%Y',date('now')) AND  strftime('%m',entry_date) = strftime('%m',date('now'))

Answer 2

You are using SQLite not mySQL so in SQLite the function to get the current date is

 date('now') 

or

 datetime('now', 'localtime')

CURDATE() is a MySQL function. The same for function Month and Year that doesn't exist in SQLite. To get years and month you have to use:

strftime('%m', myDate)//to get Month

and

strftime('%Y', myDate)//  to get Year