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

Answer 1

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();
}