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

本文关键字：大于 日期 小于 何搜索 搜索 | 更新日期: 2023-09-27 18:15:01

这是我用来创建按日期范围筛选的自定义报告的代码。第一个标准很好，但是第二个和第三个就不行。它抛出一个语法错误。有人能告诉我为什么吗?

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 + "' ";

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

它确实有助于通读生成的SQL，并实际读取语法错误消息。

假设txttodate.Text给出的值为"25- july -2014"。下面是您的代码为此生成的内容:

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

SQL Server在这种情况下生成的错误信息:

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

那是因为你的<在字符串里面。试试下面的方法，效果会好很多:

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

当我们讨论如何使它更好时，使用参数

如果你的purchase_order_date数据库字段是DATETIME类型，那么你将需要解析文本框的内容为c#日期对象，然后ToString()日期对象为SQL标准日期格式。

基本: 

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

更好:

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") + "'";
}

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

我修复了。问题是

后面有空格
cmd += "AND PURCHASE_ORDER.purchase_order_date '<" + txtfromdate.Text + "'

: 

cmd += " AND PURCHASE_ORDER.purchase_order_date <'" + txtfromdate.Text + "'
相关文章：