How to SELECT * FROM table WHERE month AND year in SQLite database by rawQuery(query, args)?

Question

I was trying to select all the columns in the table where the "month" and "year" are the same. The method rawQuery(query,args) receive an array of string[] with the a query that replaces the camps with "?".

This was some of my attempts:

"SELECT * FROM EXPENSES WHERE strftime('%m', EXPENSE_DATE)=" + month + " AND strftime('%y',EXPENSE_DATE) =" + year; 

and

"SELECT * FROM EXPENSES WHERE strftime('%Y/%m', 'EXPENSE_DATE')  =" + year + "/" + month;

and the last with the "?" replacing the args with the method in question:

String[] selectionArgs = new String[]{String.valueOf(year), String.valueOf(month)};

Cursor cursor = database.rawQuery("SELECT * FROM expenses WHERE expense_date >='?-?-01' AND expense_date <='?-?-31'", selectionArgs);

In the end after the problem appear again, i discovered that the problem was that the Object of the view was giving one DATE and the system was giving another format.

Basically i have a CALENDARVIEW that was giving me the data format of ("00-0-00") and if the variable was null the program would get the system date which was giving me the date format ("00-00-00")

The code to fill the null date was this:

//Get the default TIMEZONE
calendar = Calendar.getInstance();
//Set the format date
simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
date = simpleDateFormat.format(calendar.getTime());

and the CALENDARVIEW was this:

calendarView.setOnDateChangeListener(new CalendarView.OnDateChangeListener() {
            @Override
            public void onSelectedDayChange(@NonNull CalendarView view, int year, int month, int dayOfMonth) {

                    date = year + "-" + (month + 1) + "-" + dayOfMonth;//the Time will be blanck

            }
        });

The solution was:

calendarView.setOnDateChangeListener(new CalendarView.OnDateChangeListener() {
    @Override
    public void onSelectedDayChange(@NonNull CalendarView view, int year, int month, int dayOfMonth) {
        // the date was being save as 00-0-00 instead of 00-00-00
        if (month >10) {
            date = year + "-" + (month + 1) + "-" + dayOfMonth;//the Time will be blanck
        }else {
            date = year + "-0" + (month + 1) + "-" + dayOfMonth;//the Time will be blanck
        }
    

  }
});

and for the database in android just worked with strf:

String[] selectionArgs = new String[]{String.valueOf(year), String.valueOf(month)};
String sql = "SELECT * FROM EXPENSES WHERE "+
             "strftime('%Y', EXPENSE_DATE) = ? AND strftime('%m', EXPENSE_DATE) + 0 = ? + 0"; 
Cursor c = db.rawQuery(sql, selectionArgs); 

THAT'S IT THANKS FOR THE HELP GUYS!

Answer 1

Always use ? placeholders in the sql statement and pass the parameters as an array in the 2nd argument of rawQuery().
This is the recommended and safe way to do it.

I assume that the variables year and month are integers, this is why you use String.valueOf() to convert them to strings inside the string array.
I also assume that the column EXPENSES has the correct format YYYY-MM-DD so that strftime() will succeed to extract the year and the month.

The problem is that when you extract the month with strftime() it is returned as a string: a number left padded with a 0 if needed, say 05.
But if the value of month that you pass is 1-digit integer, say 5, the condition in the WHERE clause will become:

WHERE '05' = '5'

which returns false.
To overcome this problem you need an implicit conversion of the result of strftime() to an integer by adding 0.
For the year, since the values are always 4 digits, there is no such problem, but remember to use the '%Y' format inside strftime(), which stands for 4-digit years and not '%y' which stands for 2-digit years.

So write your code like this:

String[] selectionArgs = new String[]{String.valueOf(year), String.valueOf(month)};
String sql = "SELECT * FROM EXPENSES WHERE "+
             "strftime('%Y', EXPENSE_DATE) = ? AND strftime('%m', EXPENSE_DATE) + 0 = ? + 0"; 
Cursor c = db.rawQuery(sql, selectionArgs);