在MySQL的SQL语句中使用日期 [英] Using Dates in SQL Statements With MySQL

问题描述

我的C#代码有很大的问题.我正在尝试使用日期作为搜索条件从MySQL数据库中检索某些内容，但不返回任何行.这是我使用的代码示例:

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:

<pre lang="sql">query = "SELECT * " +<br />
                        "FROM tableName " +<br />
                        "WHERE (DateSpent BETWEEN ''" + dateFrom + "'' AND ''" +<br />
                        dateTo + "'') " +<br />
                        "ORDER BY DateSpent";</pre><br />

其中query是一个字符串变量，用于存储要执行的查询. DateSpent是我正在使用的表的列名，dateFrom是一个变量，其值是从dateTimePicker分配的. 上面的这段代码不起作用.请让我很高兴收到我的这个主要问题的帮助.

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.

推荐答案

SQL Server保存数据时间格式的方法以及.NET应用程序中的方法都不行相同.因此，您可能需要将datetime值转换为从datetimepicker传递的适当的datetime值.例如，datatimepicker返回选择的日期mm/dd/yyyy格式，因此您的查询应类似于

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

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

有关更多CONVERT SQL函数的信息，请参见. SQL Server CONVERT()函数 [ SQL注入 [

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.

尝试以这种方式找出

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

将"dateFrom"和"dateTo"更改为

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

-"s"标准格式说明符表示由 DateTimeFormatInfo定义的自定义日期和时间格式字符串.SortableDateTimePattern [ ^ ]，并且总是一样，而忽略CultureInfo设置.

或使用MySqlParamenter:

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:

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

这篇关于在MySQL的SQL语句中使用日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！