How to Select Date in SQLite

Question

I have a problem with SQlite for DateTime in UWP-app.

Assume the SQLite DB has the following data:

PurchaseDate (Date in SQLite format)
-----------------------------------
2016-09-10 11:10:10
2016-09-10 11:10:15
2016-09-10 11:10:30

Pass in this Date:

strSQLiteDate ="2016-09-10"

I just want to check if there is any row in the tblPurchase.

There is no match from below SQL-Select. What did I miss? Did I miss the hh:mm:ss part? But I just need to check the yyyy-mm-dd part.

 using (var db = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), DBPath))
 {
   var query = db.Query<tblPurchase>("Select * From tblPurchase where PurchaseDate = " + " date('" + strSQliteDate + "')");

    intcount = query.Count;

   if (intcount != 0)
   {
         return intcount;
   }
 }

Edit 1

10/8/2016 10:13:26 AM

The above date will be recreated as DateTime and SQLit.Net-PCL use it to insert into SQLite DB

string[] strAr_Date = strDate.Split('/');
string strYear = strAr_Date[0].ToString();
string strMth = strAr_Date[1].ToString();
string strDay = strAr_Date[2].ToString();

string strDateTime = strDay + "/" + strMth + "/" + strYear + " " + strTime;    

DateTime dt = DateTime.Parse(strDateTime);

Try following query: Select * From tblPurchase where date(PurchaseDate) = " + " date('" + strSQliteDate + "'). According to the documentation SQLite doesn't have a "date/time" datatype, so you have to convert it. — killexe, Aug 22 '16 at 09:29

score 1 · Answer 1 · answered Aug 22 '16 at 11:16

... where PurchaseDate = date('2016-09-10')

The date() function removes the time portion from a date/time value. But the value 2016-09-10 does not have a time portion, so it is not changed.

The PurchaseDate values still have the time portion, so you end up with a comparison like this:

... where '2016-09-10 11:10:10' = '2016-09-10'

You have to remove the time from the PurchaseDate values:

... where date(PurchaseDate) = '2016-09-10'

How to Select Date in SQLite

Edit 1

1 Answers1