How to insert date and Query date in SQLite table

Question

I am not familiar with SQLite on handling date.

The problem:

Can I insert date this way or insert any date using DateTime to create.

Order_Date = DateTime.Today

How to :

a) How to use a select or query a recordset base or Order_Date ?

b) How to select or query a recordset base on Date Range ? from this date to this date.

Thanks


      Class Order
      {
     [PrimaryKey, AutoIncrement]
        public int SId { get; set; }
        public int CustId { get; set; }     
        public string No { get; set; }        
        public string Customer { get; set; }
        public DateTime Order_Date { get; set; }

      }



   using (var db = new SQLite.SQLiteConnection(DBPath))
     {
        var newOrder = new Order()
        {
           CustId = g_intCustId,
           Customer = txtBlkCustomer.Text.Trim(),
           Order_Date = DateTime.Today   

        };

   db.Insert(newOrder);


----- Update : 


1) I wanted to know what is the proper way to insert date into a field of dateTime DataType in table as Above? using Date from DateTime.Today, DateTime.Now 

2) what fields need to add in SQLite table when enter Date with 

a) normal Date format ( dd/mm/yyyy)

b) format like :  Date with  HHMMSS

3) How to query or select Date for (2a) and (2b)?


Thanks

Farhan Ghumra Farhan Ghumra · Accepted Answer · 2013-10-10T06:15:52

Can I insert date this way or insert any date using DateTime to create. Order_Date = DateTime.Today

Yes, DateTime.Today while insertion will work. SQLite internally stores DateTime objects as string.

How to use a select or query a recordset base or Order_Date ?

using (var db = new SQLite.SQLiteConnection(ApplicationData.Current.LocalFolder.Path + "\\aaa.sqlite"))
{
    // You can use any one
    var list2 = db.Query<Order>("SELECT * FROM Order WHERE Order_Date = datetime('2013-10-01')", "");

    var list3 = db.Query<Order>("SELECT * FROM Order WHERE Order_Date = ?", DateTime.Today.AddDays(-5));
}

How to select or query a recordset base on Date Range ? from this date to this date.

using (var db = new SQLite.SQLiteConnection(ApplicationData.Current.LocalFolder.Path + "\\aaa.sqlite"))
{
    // You can use any one
    var list = db.Query<Order>("SELECT * FROM Order WHERE Order_Date BETWEEN datetime('2013-10-01') AND datetime('2013-10-07')", "");

    var list1 = db.Query<Order>("SELECT * FROM Order WHERE Order_Date BETWEEN ? AND ?", DateTime.Today.AddDays(-9), DateTime.Today.AddDays(-3));
}

SQLite Date And Time Functions

How to insert date and Query date in SQLite table

2 Answers