Using Dates in SQL Statements With MySQL

Question

0.00/5 (No votes)

See more:

I have a big problem with my C# codes. I'm trying to retrieve something from MySQL database using date as the search criteria but doest not return any row. This is a sample of the code i Used:

SQL

query = "SELECT * " +
                        "FROM tableName " +
                        "WHERE (DateSpent BETWEEN '" + dateFrom + "' AND '" +
                        dateTo + "') " +
                        "ORDER BY DateSpent";

where query is a string variable that stores the query to be executed. The DateSpent is a column name of the tables i am using and the dateFrom is a variable whose value was assigned from a dateTimePicker.
This code above, does not work. Please i will be very glad to recieve a helping hand with this major problem of mine.

Posted 15-May-11 15:49pm

BBAzar

Add a Solution

3 solutions

Add a Solution

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

Wonde Tadesse · Answer 1 · 2011-05-15T16:28:00

The way SQL server save the datatime format and in .NET application are not the same. So, you may need to convert your datetime value to appropriate datetime value that is passed from your datetimepicker. For example the datatimepicker return the picked date mm/dd/yyyy format, so your query should look like

SQL

query = "SELECT * " +
                        "FROM tableName " +
                        "WHERE (CONVERT(nvarchar(20),DateSpent,101) BETWEEN '" + dateFrom + "' AND '" +
                        dateTo + "') " +
                        "ORDER BY CONVERT(nvarchar(20),DateSpent,101)";

For more CONVERT SQL function please look.SQL Server CONVERT() Function[^]

BTW, please use parameterized query for your application. Working with plain SQL statement will expose your application for SQL Injection[^].

I hope this will help you well.

Mahendra.p25 · Answer 2 · 2011-05-15T18:57:00

Solution 2

try to find Out In this way

Query += " where OrderMaster.Order_Date >= '" + DateFrom + " " + DateTime.MinValue.ToLongTimeString() + "' and  OrderMaster.Order_Date <='" + DateTo + " " + DateTime.MaxValue.ToLongTimeString() + "'";

// It will give you accurate Result

Posted 15-May-11 18:57pm

Mahendra.p25

Kim Togo · Answer 3 · 2011-05-15T20:59:00

Change "dateFrom" and "dateTo" to

dateFrom.ToString("s") and dateTo.ToString("s")

- The "s" standard format specifier represents a custom date and time format string that is defined by the DateTimeFormatInfo.SortableDateTimePattern[^] and will alway be the same, disregard CultureInfo settings.

Or use MySqlParamenter:

C#

mySqlCommand1.CommandText = "SELECT * FROM tableName WHERE (DateSpent BETWEEN @dateFrom AND @dateTo)";
mySqlCommand1.Parameters.Add("@dateFrom", dateFrom);
mySqlCommand1.Parameters.Add("@dateTo", dateTo);

Using Dates in SQL Statements With MySQL

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Using Dates in SQL Statements With MySQL

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us