db query datetime = string中的Where子句 [英] a Where clause in db query datetime = string

问题描述

我是新手，寻求帮助。



以下代码适用于查询的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...

这篇关于db query datetime = string中的Where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！