How can I search all orders in working hours in one day from 8:00 till 22:00 c# datetimepickers?

Question

I want to search all orders in one day during working hours from 08:00 AM till 22:00 pm , i used 2 datetimepickers and select today date but when i search its not show all orders for today , I tried the following code when click search button :

private void BtnSearch_Click(object sender, EventArgs e)
{    

                string sql = @" SELECT [Order_Payments].[order_id] as 'Acc No.'
      ,order_vat as 'VAT value'
      ,vat_orders.[total_amount] as 'Total After VAT'
      ,[Order_Payments].paid_amount as 'Total Paid'
      ,[remaining_amount]
      ,vat_orders.order_date AS 'Order Date'
      ,Customers.CustName as 'Clinic'

  FROM [Order_Payments]
  inner join vat_orders on  [Order_Payments].order_id = vat_orders.ORDER_ID
  inner join Customers on  [Order_Payments].custid = Customers.CustId
  WHERE 1=1 ";

                string condition = "";
                string orderby = "";
                orderby += " ORDER BY Order_Payments.order_id";

                DateTime fromDate;
                DateTime toDate;


                if (!DateTime.TryParse(dtFromDate.Value.ToString(), out fromDate))
                {
                    System.Windows.Forms.MessageBox.Show("Invalid From Date");
                }
                else if (!DateTime.TryParse(dtToDate.Value.ToString(), out toDate))
                {
                    System.Windows.Forms.MessageBox.Show("Invalid to Date");
                }
                else
                {
                    condition += " and vat_orders.order_date between '" + fromDate + "' and '" + toDate + "'";
                }


                if (textCustId.Text != "")
                {
                    condition += " and Order_Payments.CUSTID ='" + textCustId.Text + "'";
                }


                DataTable dt = data.fireDatatable(string.Format(sql + condition + orderby));
                OrdersDataGridView.DataSource = dt;
                OrdersDataGridView.Refresh();


        }

How can I update my code and search for example today orders 1/12/2020 08:00 am till 1/12/2020 till 22:00 pm?

Or can I do it with the select statement to select date for today then select from time 00:00 till 22:00 from SQL server?

@Rebin I think the reason is he stored sql in string for next parameter ` and `. like this "where 1=1 " + " and a = @orderno" — TaiwanHotDog, Jan 13 '20 at 03:50
BTW, you should always use parameterized queries (you _might_ be doing on `fireDatatable` method but can't be sure). This kind of string concatenations are open for [SQL Injection](https://en.wikipedia.org/wiki/SQL_injection) attacks. — Soner Gönül, Jan 13 '20 at 07:06
@SonerGönül Yes you are right , i will use parametarized queries and stored procedures thank you — Abdullah, Jan 13 '20 at 11:00

score 0 · Answer 1 · answered Jan 13 '20 at 05:53

0

You need to convert the DateTime object to a string representation that SQL can use, preferable

condition += " and vat_orders.order_date between '" + fromDate.ToString("o") + "' and '" + toDate.ToString("o") + "'";

You can refer to the documentation here

and you can refer to this another question to see why use the "o" (ISO 8601 format)

answered Jan 13 '20 at 05:53

Béla Istók

31
2

how can I find orders in same selected day? , I used the code but its not searching from 00:00 till 22:00 or search for 24 hours in selected date ? thank you – Abdullah Jan 13 '20 at 06:44

score 0 · Accepted Answer · answered Jan 13 '20 at 07:52

You can solve your issue by using CAST function and you can call the date only or time only in where clause in your select statement: this link explain cast function https://www.w3schools.com/sql/func_sqlserver_cast.asp

    private void BtnSearch_Click(object sender, EventArgs e)
            {
                string sql = @" SELECT [Order_Payments].[order_id] as 'Acc No.'
          ,order_vat as 'VAT value'
          ,vat_orders.[total_amount] as 'Total After VAT'
          ,[Order_Payments].paid_amount as 'Total Paid'
          ,[remaining_amount]
          ,vat_orders.order_date 
          ,Customers.CustName as 'Clinic'

      FROM [Order_Payments]
      inner join vat_orders on  [Order_Payments].order_id = vat_orders.ORDER_ID
      inner join Customers on  [Order_Payments].custid = Customers.CustId
      WHERE cast(vat_orders.order_date as time) between '01:00:00' and '23:50:50' ";

                    string condition = "";
                    string orderby = "";
                    orderby += " ORDER BY Order_Payments.order_id";

                    DateTime fromDate;
                    DateTime toDate;


                    if (!DateTime.TryParse(dtFromDate.Value.ToString(), out fromDate))
                    {
                        System.Windows.Forms.MessageBox.Show("Invalid From Date");
                    }
                    else if (!DateTime.TryParse(dtToDate.Value.ToString(), out toDate))
                    {
                        System.Windows.Forms.MessageBox.Show("Invalid to Date");
                    }
                    else
                    {
                        condition += " and cast(vat_orders.order_date as date) between '" + fromDate + "' and '" + toDate + "'";
                    }


if (textCustId.Text != "")
            {
                condition += " and Order_Payments.CUSTID ='" + textCustId.Text + "'";
            }


            DataTable dt = data.fireDatatable(string.Format(sql + condition + orderby));
            OrdersDataGridView.DataSource = dt;
            OrdersDataGridView.Refresh();
    }

How can I search all orders in working hours in one day from 8:00 till 22:00 c# datetimepickers?

2 Answers2