Could not retrieve data from SQLite when using DateRange

Question

This is strange. Why this is not working?? I have great difficulty in getting the data even thought I have searches which say to use this format :yyyy-mm-dd

Order_Date in tblSalesOrderHeader is normal DateTime . I insert it like this : Order_Date = DateTime.Today in the SQLITE Insert operation.

How to handle this Order_Date in the SQL statement?

Would apprecaite your help.

Use the same table SalesOrderHeader 

The problem :

item(1) is working

Item(2) is not working using DateRange to retrieve records
Begin and End dates are converted in this string format : yyyy-mm-dd

I use this to convert the DateTime:

 private string DateTimeSQLite(DateTime datetime)
        {
            string dateTimeFormat = "{0}-{1}-{2}";

            return string.Format(dateTimeFormat, datetime.Year, datetime.Month, datetime.Day);
        }




//-- 1 : Working 

var query = db.Query<SalesOrderHeader>("Select * From SalesOrderHeader Where DataEntryComplete ='" + "0'" + " AND Is_SyncToNAV ='0" + "'");



//--2 : not working 

var query = db.Query <SalesOrderHeader>("Select * From SalesOrderHeader WHERE Order_Date BETWEEN '" + Begin +"'" + " AND '" + End +"'" + " AND DataEntryComplete ='" + "0'" + " AND Is_SyncToNAV ='0" + "'");

Is there a difference? I really dont know. I use My own format converter as above. — MilkBottle
At first, replace your DateTimeSQLLite with a simple ToString method: datetime.ToString("yyyy-MM-dd") that results in 2014-02-15. Does the error persist? — M. Mimpen
I notice in SQLite DB, the DateTime format is : yyyy-MM-dd 00:00:00. So I have to covert the Date into this format. I addedd 00:00:00 to the date format and it works. IS this the correct way ? — MilkBottle

stackamar stackamar · Accepted Answer · 2014-02-12T09:23:33

Try using 'DateTime.ToShortDateString()' will fetch you date string in yyyy-MM-dd format

Could not retrieve data from SQLite when using DateRange

1 Answers