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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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