locked
How to Select result with Start and End Dates in SQLite RRS feed

  • Question

  • I have the below function to get result from SQlite Database with condition to select result between Start and End Date.

    The strCurrentDate for the function is in "mm/dd/yyyy" example : 12/23/2103

    the table : itemSales has  DateTime field for  Start and End Date

    The problem:
    How to create the Select statement for Between Start Date and End Date for SQLite?

     

    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 &&

       //-- how to compare the dates : Start and End Date against the table ItemSales ??

        strCurrentDate => x.StarDate && strCurrentDate <= x.EndDate).ToListAsync();

     

     foreach (var _line in Items)
     {

      }                
    }

     Thanks

    Tuesday, December 24, 2013 10:46 AM

Answers

  • Hi FireDance,

    Take a look at the documentation from SQLite: http://www.sqlite.org/lang_datefunc.html.

    The acceptable date format is listed below, and there is no mm/dd/yyyy format supported. I would like suggest you to change your strCurrentDate as yyyy/mm/dd for the query.

    1. YYYY-MM-DD 
    2. YYYY-MM-DD HH:MM 
    3. YYYY-MM-DD HH:MM:SS 
    4. YYYY-MM-DD HH:MM:SS.SSS 
    5. YYYY-MM-DDTHH:MM 
    6. YYYY-MM-DDTHH:MM:SS 
    7. YYYY-MM-DDTHH:MM:SS.SSS 
    8. HH:MM 
    9. HH:MM:SS 
    10. HH:MM:SS.SSS 
    11. now 
    12. DDDDDDDDDD 
    
    --James


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, December 25, 2013 1:34 AM
    Moderator