通过传递sql查询比较c#中的两个日期

本文关键字：日期 两个 sql 比较 查询 | 更新日期: 2023-09-27 18:16:48

我有一个名为RETAILTRANSACTIONTABLE的表，其中有一个名为BUSINESSDATE的列，其日期格式为(yyyy-mm-dd) 2015-05-22。

我想比较一下这个日期和批次。StartDateTime的格式为(dd-mm-yyyy hh:mm:ss) 05-10-2015 12:09:03.

var dateAndTime = batch.StartDateTime;
var date = dateAndTime.ToShortDateString();
string query = "SELECT COUNT(discamount) AS DISCOUNTCOUNT "+
                " FROM ax.RETAILTRANSACTIONTABLE where "+
                "(CONVERT(VARCHAR(10),BUSINESSDATE,105) >= '" + date
                + "') and DISCAMOUNT > 0

使用上面的查询给了我列的所有值，我只想要大于或等于批处理日期的值的计数

通过传递sql查询比较c#中的两个日期

您可以使用:

var dateAndTime = batch.StartDateTime;
var date = dateAndTime.ToShortDateString();
string query = "SELECT COUNT(discamount) AS DISCOUNTCOUNT "+
            " FROM ax.RETAILTRANSACTIONTABLE "+
            " WHERE BUSINESSDATE >= CONVERT(DATE, '" + date + "', 105) 
            and DISCAMOUNT > 0 "

如果存在索引，则在BUSINESSDATE上使用索引。

或者像@FelixPamittan说的那样，使用带binding的预处理语句:

var dateAndTime = batch.StartDateTime;
var date = dateAndTime.ToShortDateString();
string query = "SELECT COUNT(discamount) AS DISCOUNTCOUNT "+
            " FROM ax.RETAILTRANSACTIONTABLE "+
            " WHERE BUSINESSDATE >= :theDate 
            and DISCAMOUNT > 0 "

我想这对你会有用的

var dateAndTime = batch.StartDateTime;
var date = dateAndTime.ToShortDateString();
string query = "SELECT COUNT(discamount) AS DISCOUNTCOUNT " +
                " FROM ax.RETAILTRANSACTIONTABLE WHERE" +
                "BUSINESSDATE > = (CONVERT(VARCHAR, " + @dateParam + " ,105) >= '" + 
                + "') and DISCAMOUNT > 0
SqlConnection connection = new SqlConnection(/* connection info */);
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("dateParam", date.ToString("yyyy/mm/dd"));

在以上答案的帮助下，我对其进行了修改，最终得到了解决方案

var dateAndTime = batch.StartDateTime;
var date = dateAndTime.ToString("yyyy-MM-dd");
string query = "SELECT COUNT(discamount) AS DISCOUNTCOUNT "+
               " FROM ax.RETAILTRANSACTIONTABLE where "+                    
               "BUSINESSDATE >= '" + date + "'" + 
               " and DISCAMOUNT > 0
相关文章：