Get count from SQLITE PCL for a specific month

Question

I am using Xamarin.Forms and C#. I am trying to get the count of Products for each month in a year.

string[] months = new string[] { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" };

var montlyCount = new Dictionary<string, int>();
foreach (var month in months)
{
      var count = App.Database.GetCount(month);
      montlyCount.Add(month, count);
}

Database.cs:

public int GetCount(string month)
{
    return database.ExecuteScalar<int>("SELECT COUNT(*) FROM Products WHERE MONTH(ProductEntryDate) = ?;", month);
}

However, there is no such function "MONTH" with SQLite PCL. How can I get the count where month = "January", for example?

Answer 1

As you already found out there is no function like MONTH() in SQLite.
The function that you can use to extract the month from a properly formatted date (YYYY-MM-DD) is strftime() which will return a left 0 padded string which is the number of the month:

SELECT COUNT(*) 
FROM Products 
WHERE strftime('%m', ProductEntryDate) = ?

So you must pass as argument a string like '01', '02', ...., '12' and not the month's name.

Or you can pass an integer 1, 2, ..., 12 if you implicitly convert the return value of strftime() to an integer by adding 0:

WHERE strftime('%m', ProductEntryDate) + 0 = ?