在MySQL的SQL语句中使用日期 | 在MySQL的SQL语句中使用日期

本文介绍了在MySQL的SQL语句中使用日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的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.

推荐答案

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语句将为 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.

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

// It will give you accurate Result

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

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