I have created a record on date 7th March. But when I'm trying to retrieve the record, it is being displayed as 4th of March. What can be the problem???
What I have tried:
private void FillList()
{
if (listView1.Items.Count > 0)
listView1.Items.Clear();
using (PetaPoco.Database db = new Database(new Db().Connection))
{
IEnumerable<reportinvoiceitems> items;
Doctor doctor = db.Single<doctor>(this._doctorId);
//Following is the Query I've written
string query = String.Format("select invoiceitem._ROWID_ as sr, strftime('%d-%m-%Y', invoice.date) as date,doctor.name as DoctorName ,product.name as productname,invoiceitem.range, invoiceitem.id ,invoiceitem.quantity, invoiceitem.price, invoiceitem.vat, ((invoiceitem.quantity * invoiceitem.price) + ((invoiceitem.price * invoiceitem.quantity) * (invoiceitem.vat / 100))) as amount, invoiceitem.remark from invoiceitem inner join invoice on invoiceitem.invoice_id = invoice.id inner join doctor on invoice.doctor_id = doctor.id inner join product on invoiceitem.product_id = product.id where invoice.doctor_id = {0} and invoice.date between '{1}' and '{2}'", _doctorId, _from.ToString("yyyy-MM-dd HH:mm:00"), _to.AddMinutes(1439).ToString("yyyy-MM-dd HH:mm:00"));
items = db.Query<reportinvoiceitems>(query);
decimal total = 0;
int v = this._vat;
foreach (var item in items)
{
decimal amount = Convert.ToDecimal(item.Quantity) * Convert.ToDecimal(item.Price);
amount = amount + (amount * v / 100);
total += amount;
ListViewItem listViewItem = new ListViewItem(new string[] { item.Sr.ToString(), item.Date, item.ProductName, item.DoctorName, item.Remark, item.Range, item.Quantity, item.Price, item.VAT, item.Amount });
listViewItem.Tag = item;
listView1.Items.Add(listViewItem);
}
label1.Text = doctor.name;
label2.Text = total.ToString();
}
}