How to select if pass-in-Date is between DateRange

Question

I have a table which has DateTime fields as below:

 class ItemSales
    {
        [PrimaryKey, AutoIncrement]
        public int SpId { get; set; }

        public string CompanyName { get; set; }
        public string ItemNo { get; set; }     
        public DateTime StartDate { get; set; }
        public decimal UnitPrice { get; set; }
        public decimal MinimumQuantity { get; set; }
        public DateTime EndDate { get; set; }       
        public decimal Cost { get; set; }      
        public DateTime InsertDate { get; set; }

       ---- others
    }

---
others

The problem:

How to query where I need to check if the pass-in string date strCurrentDate (DateTime.Now) is between StartDate and EndDate which are in the table?

If I pass in the current date as yyyy-mm-dd and I have the following function:

--- Update

error Message:

Operator '>' cannot be applied to operands of type 'string' and 'System.DateTime'



 private async void GetActualPrice(string Cpy, string No, string strCurrentDate,string strSelectedUoM)
 {
  var db = new SQLiteAsyncConnection(dbPath);

var Items = await db.Table < ItemSales >().Where(x => x.CompanyName == Cpy && x.ItemNo == No && strCurrentDate > x.StarDate && strCurrentDate < x.EndDate).ToListAsync();



 foreach (var _line in Items)
 {

  }                 
}



No need to convert the Start and End Date into string ??? 

 "'" + strCurrentDate + "'" + " BETWEEN StartDate And EndDate");

This is not a duplicate question. The difference is that I am passing in the date in string and I need to check against the dateTime fields in the table. — MilkBottle
Have you read through the answers in the proposed duplicate question? Dates have to be specifically formatted in sqlite to be treated as dates since they are stored as strings in the DB. — Metro Smurf
I read it. For my case, the pass-in string date in format yyyy-mm-dd is used to check against the datetime fields as Compare to DateTime field check against string date format. I am testing it and not sure if this is workable? — MilkBottle

BobHy BobHy · Accepted Answer · 2013-12-24T02:40:48

By 'current date', do you mean the time at which you submit the query to the database? You can format the current date as a constant in the query, like : Also, you need to provide a datatype for the query response, either by:

List<String> Items = await db.QueryAsync( ...

or

var Items = await db.QueryAsync<List<String>>( ...

then

    "Select * From ItemSales Where CompanyName ='" + Cpy + "' AND ItemNo ='" + No + "' AND '" + DateTime.UtcNow.ToString("yyyy/M/d") + "' BETWEEN StartDate and EndDate");

Date will have form 2013/12/23. I assume dates in your database are GMT.

(Edit: changed string.format() to string concatenation...) (Edit: based on error message provided, C# needs to be told datatype returned by .QueryAsync(). There may also be a problem with SQL query syntax, but we won't find it till the datatype declaration is fixed.)

How to select if pass-in-Date is between DateRange

2 Answers