如何搜索小于或大于X的日期

This is the code I'm using to create a custom report that filters by a date range. The first criteria works fine, however the second and third do not. It throws a syntax error. Can anyone tell me why?

这是我用来创建按日期范围过滤的自定义报告的代码。第一个标准工作正常,但第二个和第三个标准没有。它会引发语法错误。谁能告诉我为什么?

string cmd = "SELECT PURCHASE_ORDER.cid, PURCHASE_ORDER.deptid, PURCHASE_ORDER.procid, PURCHASE_ORDER.purchase_order_no, PURCHASE_ORDER.requesition_no, PURCHASE_ORDER.contract_no, PURCHASE_ORDER.purchase_order_date, COMPANY.company_name,  DEPARTMENT.department_name, PROCUREMENT_METH.method FROM PURCHASE_ORDER INNER JOIN PROCUREMENT_METH ON PURCHASE_ORDER.procid = PROCUREMENT_METH.procid INNER JOIN COMPANY ON PURCHASE_ORDER.cid = COMPANY.cid INNER JOIN DEPARTMENT ON PURCHASE_ORDER.deptid = DEPARTMENT.deptid WHERE PURCHASE_ORDER.deptid = DEPARTMENT.deptid";

// Check Criteras
if (!string.IsNullOrWhiteSpace(ddlDepartment.Text) && !ddlDepartment.Text.Equals("0"))
   cmd += " AND PURCHASE_ORDER.deptid LIKE '%" + ddlDepartment.Text + "%' ";

if (!string.IsNullOrWhiteSpace(txtfromdate.Text))
   cmd += "AND PURCHASE_ORDER.purchase_order_date '<" + txtfromdate.Text + "' ";

if (!string.IsNullOrWhiteSpace(txttodate.Text))
   cmd += "AND PURCHASE_ORDER.purchase_order_date '<" + txttodate.Text + "' ";

3 个解决方案

#1

It really helps to actually read through your generated SQL, and actually read the syntax error messages.

真正有助于实际读取生成的SQL,并实际读取语法错误消息。

Let's say txttodate.Text gives the value "25-Jul-2014". Here's what your code generates for that:

假设txttodate.Text给出了“25-Jul-2014”的值。以下是您的代码为此生成的内容:

AND PURCHASE_ORDER.purchase_order_date '<25-Jul-2014'

And the error message SQL Server generates in that situation:

并且SQL Server在这种情况下生成错误消息:

Error 102: Incorrect syntax near '<25-Jul-2014'.

错误102:'<25-Jul-2014'附近的语法不正确。

That's because your < is inside the string. Try this way instead, it'll work much better:

那是因为你的 <在字符串里面。试试这种方式,它会更好地工作:< p>

cmd += "AND PURCHASE_ORDER.purchase_order_date < '" + txttodate.Text + "' ";

And while we're on the topic of making this better, use parameters.

虽然我们正在讨论使这更好的主题,但使用参数。

#2

If your purchase_order_date database field is of type DATETIME then you will need to parse the contents of the textbox into a C# Date object and then ToString() the Date object to the SQL standard date format.

如果purchase_order_date数据库字段的类型为DATETIME,则需要将文本框的内容解析为C#Date对象,然后将ToString()的Date对象解析为SQL标准日期格式。

Rudimentary:

DateTime time = DateTime.Parse(txttodate.Text.Trim().Replace("'","''"));
cmd += "AND PURCHASE_ORDER.purchase_order_date '<" + datetime.ToString("yyyy-MM-dd") + "' ";

Better:

DateTime dateTime,dateTime2;

if (DateTime.TryParse(txttodate.Text.Trim().Replace("'","''"), out dateTime1) && DateTime.TryParse(txtfromdate.Text.Trim().Replace("'","''"), out dateTime2))
{
    cmd += "AND PURCHASE_ORDER.purchase_order_date BETWEEN + '" + dateTime1.ToString("yyyy-MM-dd") + "' AND '" + dateTime2.ToString("yyyy-MM-dd") + "'";
}

Also I think you are probably looking to use the BETWEEN SQL operator to determine any purchase_order_dates between the FROM date and the TO date.

此外,我认为您可能希望使用BETWEEN SQL运算符来确定FROM日期和TO日期之间的任何purchase_order_dates。

#3

-1

I fixed it. the problem was a space after

我修好了它。问题是之后的空间

cmd += "AND PURCHASE_ORDER.purchase_order_date '<" + txtfromdate.Text + "'

like this:

cmd += " AND PURCHASE_ORDER.purchase_order_date <'" + txtfromdate.Text + "'

#1