Weekly Data is not Returned using Between in SQLite

Related searches
 @Override
public void onCreate(SQLiteDatabase db) {
    this.db = db;
    db.execSQL(
            "CREATE TABLE `Transaction` (" +
            "_id integer primary key autoincrement," +
            "`categoryID` INT NOT NULL , " +
            "`incomeAmount` INT NOT NULL, " +
            "`expenseAmount` INT NOT NULL, " +
            "`note` VARCHAR(50) NOT NULL , " +
            "`transactionDate` INT NOT NULL, " +
            "`repeatType` INT NOT NULL  , " +
            "`transactionType` BOOLEAN NOT NULL )"
    );
}
public Cursor readDatabaseTransactionWeekly(Date date){
    Calendar cal = Calendar.getInstance();
    cal.setTime(date);
    cal.set(Calendar.HOUR_OF_DAY, 0);
    cal.clear(Calendar.MINUTE);
    cal.clear(Calendar.SECOND);
    cal.clear(Calendar.MILLISECOND);
    cal.set(Calendar.DAY_OF_WEEK, cal.getFirstDayOfWeek());

    Date startWeek = cal.getTime();
    Calendar nextWeelCal = Calendar.getInstance();
    nextWeelCal.setTime(cal.getTime());
    nextWeelCal.add(Calendar.WEEK_OF_MONTH,1);
    Date nextWeek = nextWeelCal.getTime();
    Log.d(TAG, "readDatabaseTransactionWeekly: "+startWeek+" to "+nextWeek+"");
    this.db = getReadableDatabase();
    Cursor transactionRecordWeekly = this.db.rawQuery("SELECT * FROM `Transaction` WHERE transactionDate BETWEEN '"+startWeek+"' AND '"+nextWeek+"'",null);
    transactionRecordWeekly.moveToFirst();
    return transactionRecordWeekly;
}

public Cursor readDatabaseTransactionMonthly(Date date){
    Calendar cal = Calendar.getInstance();
    cal.setTime(date);
    cal.set(Calendar.HOUR_OF_DAY, 0);
    cal.clear(Calendar.MINUTE);
    cal.clear(Calendar.SECOND);
    cal.clear(Calendar.MILLISECOND);
    cal.set(Calendar.DAY_OF_MONTH, 1);
    Date startMonth = cal.getTime();
    Calendar nextMonthCal = Calendar.getInstance();
    nextMonthCal.setTime(cal.getTime());
    nextMonthCal.add(Calendar.MONTH,1);
    Date nextMonth = nextMonthCal.getTime();
    this.db = getReadableDatabase();
    Cursor transactionRecordMonthly = this.db.rawQuery("SELECT * FROM `Transaction` WHERE transactionDate BETWEEN '"+startMonth+"' AND '"+nextMonth+"'",null);
    transactionRecordMonthly.moveToFirst();
    return transactionRecordMonthly;
}

I am trying to read data from SQLite in Andoroid on Weekly and Monthly Bases and I am using SQLite BETWEEN Statement. But It's not working as expected. The weekly section doesnot return any data at all while Monthly works fine does not show data of current week. I looked into 4 other stackoverflow question but couldnot solve it.

I think the Calendar.WEEK_OF_MONTH does not do what you expect. Try adding 7 days instead.

SQLite BETWEEN Operator By Practical Examples, SQLite NOT BETWEEN numeric values example. To find the invoices whose total are not between 1 and 20, you use the NOT BETWEEN operator as shown in the​  If you did not find the tutorial that you are looking for, you can use the following search box. In case the tutorial is not available, you can request for it using the request for a SQLite tutorial form.

cal.set(Calendar.DAY_OF_WEEK, cal.getFirstDayOfWeek()); sets the date to Monday 18 June

SQLite: BETWEEN Condition, This SQLite tutorial explains how to use the SQLite BETWEEN condition with This website would not exist without the advertisements we display and your kind Create Table · Create Table As · Data Types · Detach Database · Drop Table This SQLite BETWEEN example would return all rows from the employees table  SQLite does not have a storage class for storing dates and/or times. Instead, the built-in Date and Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values: TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).

I changed the type of "transactionDate" field to "long" and then inserted the dates into the database by converting dates in milliseconds. This is the solution that worked for me and maybe it's not a good one but it worked. I am posting that it may help anyone else.

Last one week, month, year record from date field by DATE_SUB, DATE_SUB : Getting the last one week or two month or one year records from Some time we have to collect last 7 or 15 days or X days (or month, year or week) data from MySQL table. Note that this query will return all future dates also. We can collect records between a particular date ranges by using between  In SQL Server you should do it like this. If you use something else you have to replace dateadd with something else. @Zapnologica The greatest benefit is that it will return all rows for a specific day. This answer will not return rows with a time value of the last second of the day.

The Definitive Guide to SQLite, It does not start a transaction or get a lock. It works directly with the compiler, which simply prepares the query for execution. If another connection alters the database schema, between the time you prepare a statement and For SQL statements that don't return data, the first call to sqlite?_step() executes the command in  However, for SQLite, that property is often set to -1 due to the nature of how SQLite produces results. Short of a COUNT() query first you often won't know the number of results returned. This is because SQLite produces rows as it finds them in the database, and won't itself know how many rows are produced until the end of the database is reached.

Learning SQLite for iOS, However, there is another way to update data to the destination table from a If multiple rows are returned using these types of queries, then operators such as a subquery as long as it is not the BETWEEN operator itself: Data integrity Data  Secondly, your app does not have to package SQLite libraries. Instead, your app can use the version of SQLite that comes installed with Windows. This helps you in a few ways. ✔️ Reduces the size of your application because you don't have to download the SQLite binary, and then package it as part of your application.

The C language interface to SQLite Version 2, This document is retained as part of the historical record of SQLite. Modern The SQLite library is designed to be very easy to use from a C or C++ program. This value is returned if the callback function returns non-zero. SQLite will not store more than about 1 megabyte of data in a single row of a single table. SQLite NOT IN "NOT IN" operand is the opposite of the IN operator. But with the same syntax; it takes one operand and a list of operands. It will return true if the first operand value not equal to one of the operands' value from the list. i.e., it will return true (0) if the list of operands doesn't contain the first operand.

Comments
  • Tried adding 7 days in Calender.DAY_OF_Month but not output again.
  • Anyway this is the Start and End Dates of week with Calender.WEEK_OF_Month which looks fine. Sun Jun 24 00:00:00 GMT+05:00 2018 to Sun Jul 01 00:00:00 GMT+05:00 2018
  • Tried that before as well. But it App stops unexpectedly and it shows this error. Caused By : SQL(query) error or missing database. (near "Jun": syntax error (code 1): , while compiling: SELECT * FROM Transaction WHERE transactionDate BETWEEN Sun Jun 24 00:00:00 GMT+05:00 2018 AND Sun Jul 01 00:00:00 GMT+05:00 2018)
  • Where? nextWeelCal.setTime(cal.getTimeInMillis()); iI cahnged it here but IDE shows error since nextWeek is Date Type.
  • No output again. I cahnged startWeek and nextWeek to long. But still nothing. :-(
  • changing transactionDate to DATE is also not working.
  • Can you provide some code of how you store the date in db?