问题描述
我是新手，寻求帮助。 以下代码适用于查询的where子句，其中存在帖子请求。 一切正常，直到按日期过滤。 字段date_deliv_required在数据库中设置为datetime。变量datereq是格式为yyyy-mm-dd的字符串。 date_deliv_required输出为yyyy-mm-dd h：i：s（我认为） 如何进行这种类型的SQL查询
Hi am a newbie to this and looking for assistance. the following code applies to a where clause to a query whereby a post request exists. Everything is ok up until filtering by date. the field date_deliv_required is set to datetime in the database. variable datereq is a string in format yyyy-mm-dd. date_deliv_required outputs as yyyy-mm-dd h:i:s (I think) How would one this type of SQL query
select * from table where date_deliv_required = 'yyyy-mm-dd' ，代码如下：
with the below code:
public ActionResult Index(string zone, string datereq)
{
var deliverylists = db.deliverylists.Include(d => d.branch);
if (!String.IsNullOrEmpty(zone))
{
deliverylists = deliverylists.Where(d => d.id_sales_zone.Equals(zone));
}
//problem zone start
if (!String.IsNullOrEmpty(datereq))
{
deliverylists = deliverylists.Where(d => d.date_deliv_required.Equals(datereq));
}
//problem zone end
return View(deliverylists.ToList());
}
推荐答案
就个人而言，我会使用.Net提供的一种技术将参数传递给数据库查询。使用参数可确保正确处理日期格式和字符串等内容;当您的过滤器值作为用户的文本捕获时，它们对于防止SQL注入攻击也很重要。 这是一个老式的方法，Linq和实体框架提供了更多OO的方法，但可以隐藏你的应用程序和数据库之间发生的事情（不是总是有问题，但是当你试图学习概念时它没有帮助）： Personally, I'd use one of the technologies .Net offers for passing parameters into database queries. Use of parameters ensures that things like date formats and strings are handled correctly; they are also essential for preventing SQL injection attacks when your filter values are captured as text from a user. Here's an old-school approach, Linq and Entity Framework offer ways to do it that are more OO but can hide what's going on between your app and the database (not always a problem, but it doesn't help when you're trying to learn concepts):
public static void DoStuff()
{
const string CONNECTION = "write me - the connection string";
const string SQL = "SELECT * "+
" FROM table "+
" WHERE date_deliv_required = @DateDelivered";
var dateDelivered = Convert.ToDateTime("01/08/2008");
DataTable dt = new DataTable();
using (var conn = new SqlConnection(CONNECTION))
{
using (var cmd = new SqlCommand(SQL, conn))
{
conn.Open();
cmd.Parameters.AddWithValue("@DateDelivered", dateDelivered);
cmd.CommandType = CommandType.Text;
using (var ada = new SqlDataAdapter(cmd))
{
ada.Fill(dt);
}
}
}
// Do stuff with the rows in the data table dt...
}
string date =01/08/2008; DateTime dt = Convert.ToDateTime（date）; int month = dt.month; int year = dt.year; int days = dt.days; string date = "01/08/2008"; DateTime dt = Convert.ToDateTime(date); int month =dt.month; int year=dt.year; int days=dt.days;
select * from table where MONTH(date_deliv_required)=month and YEAR(date_deliv_required)=year and DAY(date_deliv_required)=days试试这个，
Try this,
通过转换为具有所需格式的Datetime进行比较。 Compare by converting into Datetime with the required format.
var dateToCompare = DateTime.ParseExact(datereq, "yyyy-MM-dd", CultureInfo.InvariantCulture);
deliverylists = deliverylists.Where(d => DateTime.Compare(d.date_deliv_required.Date, dateToCompare .Date) <= 0);希望这有帮助......
Hope this helps...