How to filter date to date from two datetimepicker in database SQL C#

Question

0.00/5 (No votes)

See more:

Is it possible to filter date to date from sql
In database for date I use field type varchar[15] with dd-MMM-yy format
ex. 04-Nov-21

string sql = "select purchaseorder.Product_ID as 'ID',productinfo.product_name as 'Name', purchaseorder.Pp_Qty as 'Quantity', purchaseorder.Pp_Total as 'Total Price(Baht)',purchaseorder.Pp_Date as 'Purchased Date' from purchaseorder inner join productinfo on purchaseorder.Product_ID = productinfo.product_id " +
                "where convert Pp_Date between '" + dateTimePicker1.Value.ToString("dd/MMM/yy") + "'and'" + dateTimePicker2.Value.ToString("dd/MMM/yy") + "'";
DataTable t1 = m1.GetDataTable(sql);
dataGridView1.DataSource = t1;

What I have tried:

string sql = "select purchaseorder.Product_ID as 'ID',productinfo.product_name as 'Name', purchaseorder.Pp_Qty as 'Quantity', purchaseorder.Pp_Total as 'Total Price(Baht)',purchaseorder.Pp_Date as 'Purchased Date' from purchaseorder inner join productinfo on purchaseorder.Product_ID = productinfo.product_id " +
                "where convert Pp_Date between '" + dateTimePicker1.Value.ToString("dd/MMM/yy") + "'and'" + dateTimePicker2.Value.ToString("dd/MMM/yy") + "'";
            DataTable t1 = m1.GetDataTable(sql);
            dataGridView1.DataSource = t1;

tried this but it didn't work

Posted 3-Nov-21 18:21pm

Member 14961132

Updated 3-Nov-21 21:14pm

Add a Solution

Comments

phil.o 4-Nov-21 0:23am

Which type is the Pp_Date column?

Member 14961132 4-Nov-21 2:38am

varchar[15]

phil.o 4-Nov-21 2:52am

There is your issue. You should always use the database type which is adapted to the data. Datetime values are a nightmare to handle when stored as strings.
If you use datetime type, it means you can compare two values using common < and > operators. If you use strings, you cannot compare values directly, and so have to split strings to get relevant parts; not to mention datetime format issues...
I'll write a solution to show you how to write your query for a column with proper type. The query for a datetime stored as string is awful, spending time on it is useless since it will resort to very poor query performances anyway.

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2021-11-03T20:54:00

There are two problems here: one serious that you don't think you've met yet, and the other causing the problem you have noticed.

The serious one is simple: never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

And that's related to the problem you have noticed: never store data in a text based filed unless it is text based data. Always use an appropriate datatype: DECIMAL for money, INT for integers, and DATE, DATETIME, or DATETIME2 for dates.
This is causing your problem because string based comparisons are always based on the first different character that is encountered when comparing the two strings. Which means that dates in string format compare very badly: "01/01/2021" is before "31/12/2020" because '0' is before '3'.

And there is more: even if you store dates ad DATETIME then unless you pass DateTime values from your app as a parameter, you will still get errors because string formatted dates have to be interpreted by SQL as DATETIME first, and they don't get any context and make assumptions about the format: is "01/02/03" the 1st Feb 2003, or 2nd Jan 2003, or 3rd Feb 2001? They are all valid: US, European, Japanese / ISO formats.

You need to change your whole app to fix the first problem, then change your database to fix the second as well.

phil.o · Answer 2 · 2021-11-03T21:14:00

As said in my comment, you are experiencing something very common: having to write bloated and inefficient SQL code because you are not using the proper datatype.

In short:

Use (one of) the datetime datatype(s) which most suits your data.
Use parameterized queries to perform comparisons.

C#

// Wrap connection in using block, so that it is automatically disposed properly.
using (SqlConnection cnx = /* Initialize connection here */)
{
   cnx.Open();

   string sql = "SELECT purchaseorder.Product_ID AS 'ID', productinfo.product_name AS 'Name', purchaseorder.Pp_Qty AS 'Quantity', purchaseorder.Pp_Total AS 'Total Price(Baht)', purchaseorder.Pp_Date AS 'Purchased Date' FROM purchaseorder INNER JOIN productinfo ON purchaseorder.Product_ID = productinfo.product_id WHERE purchaseorder.Pp_Date BETWEEN @datelow AND @datehigh;";

   using (SqlCommand cmd = new SqlCommand(sql, cnx))
   {
      // Add parameters to command object, which will pass actual datetime values.
      cmd.Parameters.AddWithValue("@datelow", dateTimePicker1.Value);
      cmd.Parameters.AddWithValue("@datehigh", dateTimePicker2.Value);

      // Execute the query and get a data reader.
      var reader = cmd.ExecuteReader();

      /* From here populate the datatable.
       * You will need to adjust the class defining the type of m1 variable
       * to return a datatable from a data reader rather than from
       * a query string. */
   }
}

How to filter date to date from two datetimepicker in database SQL C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to filter date to date from two datetimepicker in database SQL C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us