Xamarin SQLite-Pcl-Net DateTIme in Query

Question

I am using Xamarin Prism along with Sqlite-pcl-net Nuget .

While I write Query to obtain the elements of table between a range of dates as follows

 public Task<List<JournalModel>> GetRecieptListAsync(DateTime startdate,DateTime enddate)
        {
            return database.QueryAsync<JournalModel>("SELECT * FROM [JournalModel] WHERE ( [PromisedDate] BETWEEN " + startdate + " AND " + enddate + ")");
        }

StartD and EndD are variables of DateTime Datatype of Format:

StartD MM/dd/yyyy 00:00:00

EndD MM/dd/yyyy 23:59:59

After executing this Query I get a

SQLite.SQLiteException: near "12": syntax error

Which means at the hour of StartD. I hope You know it takes 12:00:00Am at StartD in the Database. Comment if I have to make more clearifications.

Gustavo Gustavo · Accepted Answer · 2018-11-12T16:41:14

In SQLite-pcl-net the default format for Datetime is Ticks.

You can change startdate to startdate.Ticks and enddate to enddate.Ticks.

return database.QueryAsync<JournalModel>("SELECT * FROM [JournalModel] WHERE ( [PromisedDate] BETWEEN " + startdate.Ticks + " AND " + enddate.Ticks + ")");

Xamarin SQLite-Pcl-Net DateTIme in Query

1 Answers