Fetching data from sql databse in flutter datewise?

Question

I am making an app in flutter and I am implementing SQL database using the sqflite plugin. In this app, users can create transactions by filling out the form below Then the user can verify their transactions and they also will be having an option to edit the transaction in case they have made a mistake Now I want to add a feature by which they can get the sum of expenses they have done in a particular month on this screen below It is showing null but it should show the sum of the expenses

The code for my query is

Future getDataJan() async{
final db = await database;
sumJan=await db.rawQuery(
    'SELECT SUM(AMOUNT) FROM EXPENSES WHERE DATE(DATETIME) >= ? AND DATE(DATETIME) <= ?',
    [2021-01-01, 2021-01-31] ).then(Sqflite.firstIntValue);
print(sumJan);
finalJan=sumJan.toString();
}

Database creation:

Future<Database> createDatabase() async {
String dbPath = await getDatabasesPath();

return await openDatabase(
  join(dbPath, 'expenseDB.db'),
  version: 1,
  onCreate: (Database database, int version) async {
    print("Creating expense table");

    await database.execute(
      "CREATE TABLE $TABLE_EXPENSES ("
      "$COLUMN_ID INTEGER PRIMARY KEY,"
      "$COLUMN_NAME TEXT,"
      "$COLUMN_AMOUNT INTEGER,"
      "$COLUMN_UNNECESSARYEXPENSES INTEGER,"
      "$COLUMN_CATEGORY TEXT,"
      "$COLUMN_DATETIME TEXT"
      ")",
    );
  },
);
}

Mapping Database(I think it's not working because of string while mapping):

Map<String, dynamic> toMap() {
var map = <String, dynamic>{
  DatabaseProvider.COLUMN_NAME: name,
  DatabaseProvider.COLUMN_AMOUNT: amount,
  DatabaseProvider.COLUMN_UNNECESSARYEXPENSES: isUnnecessaryExpenses ? 1 : 0,
  DatabaseProvider.COLUMN_CATEGORY: category,
  DatabaseProvider.COLUMN_DATETIME: pickedDate,
};

if (id != null) {
  map[DatabaseProvider.COLUMN_ID] = id;
}

return map;
}

Entries of database:

Edit: This is my code for creating a Widget to call for datepicking:

  Widget DatePicker() {

   showDatePicker(context: context, initialDate: DateTime.now(), firstDate: DateTime.parse("2020-01-01 00:00:01Z"), lastDate: DateTime.now()
  ).then((value){
    if (value == null){
      return;
     }
     _dateTime = value;
     ////saving in string
     pickedDate = DateFormat('yyyy-MM-dd').format(_dateTime);
     });
   }

Error given after the suggestion by Problematic Dude :

lib/db/database_provider.dart:111:59: Error: Expected ',' before this.
  'SELECT SUM(AMOUNT) FROM EXPENSES WHERE strftime('%m' , date(datetime)) ==   01').then(Sqflite.firstIntValue);
                                                       ^^^^^^^^^^^^^^^^^^^^^^^^^^
    lib/db/database_provider.dart:111:58: Error: The getter 'm' isn't defined  for the class 'DatabaseProvider'.
    - 'DatabaseProvider' is from 'package:finance_manager/db /database_provider.dart' ('lib/db/database_provider.dart').
    Try correcting the name to the name of an existing getter, or defining a   getter or field named 'm'.
  'SELECT SUM(AMOUNT) FROM EXPENSES WHERE strftime('%m' , date(datetime)) == 01').then(Sqflite.firstIntValue);
                                                     ^
   lib/db/database_provider.dart:111:57: Error: The operator '%' isn't defined for the class 'String'.
   Try correcting the operator to an existing operator, or defining a '%' operator.
  'SELECT SUM(AMOUNT) FROM EXPENSES WHERE strftime('%m' , date(datetime)) ==   01').then(Sqflite.firstIntValue);
                                                    ^
  lib/db/database_provider.dart:111:59: Error: The argument type 'String' can't be assigned to the parameter type 'List<dynamic>'.
- 'List' is from 'dart:core'.
     'SELECT SUM(AMOUNT) FROM EXPENSES WHERE strftime('%m' , date(datetime)) == 01').then(Sqflite.firstIntValue);

Update

I opened the database using database inspector in android studio and tried running the following queries in it:

SELECT SUM(AMOUNT) FROM EXPENSES

SELECT SUM(AMOUNT) FROM EXPENSES WHERE DATE(DATETIME) >= 2021-01-01 and  DATE(DATETIME) <= 2021-01-31

The above query works perfectly fine and returns the sum of the column amount, but as soon as I run the second query it gives me null again, I think I am saving the date in the wrong manner.

Please help.

Thanks for your replies

Can you show how you are converting dateTime to string for storing it in DB? — Nikhil Badyal
– Nikhil Badyal, Commented Apr 19, 2021 at 11:08
Hello thanks for your reply, I have added the code for it please check it once again. — Sanchay Kasturey
– Sanchay Kasturey, Commented Apr 19, 2021 at 12:39
Try executing this SELECT SUM(AMOUNT) FROM EXPENSES WHERE strftime('%m' , date(datetime)) == 01 — Nikhil Badyal
– Nikhil Badyal, Commented Apr 19, 2021 at 13:18
I have implemented it like this: Future getDataJan() async{ final db = await database; sumJan=await db.rawQuery( 'SELECT SUM(AMOUNT) FROM EXPENSES WHERE strftime('%m' , date(datetime)) == 01').then(Sqflite.firstIntValue); print(sumJan); finalJan=sumJan.toString(); } — Sanchay Kasturey
– Sanchay Kasturey, Commented Apr 19, 2021 at 13:48

Aman Verma · Accepted Answer · 2021-04-20 16:33:31Z

2

Your function is missing quotes around date

Future getDataJan() async{
final db = await database;
sumJan=await db.rawQuery(
    'SELECT SUM(AMOUNT) FROM EXPENSES WHERE DATE(DATETIME) >= ? AND DATE(DATETIME) <= ?',
    ['2021-01-01', '2021-01-31'] ).then(Sqflite.firstIntValue);
print(sumJan);
finalJan=sumJan.toString();
}

answered Apr 20, 2021 at 16:33

Aman Verma

9187 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

Fetching data from sql databse in flutter datewise?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related