如何从DateTime值中删除时间？ [英] How to remove time from DateTime value?

问题描述

当我将datetimepicker的值添加到db时，它会显示以下错误，

12'附近的语法不正确。

When I'm adding value from datetimepicker to db, it shows following error,
"Incorrect syntax near '12'."

int id = 1;
Datetime idate = dtp.value.date; //it stores 13-07-2014 12:00:00 AM in idate

//query
cmd.CommandText="insert into tbl(id,idate) values("+ id +","+ idate +")";
cmd.ExecuteNonQuery();

我已将DateTimePicker的自定义格式设置为dd-MM-yyyy。

我想删除时间部分 13-07-2014 12:00:00 AM

推荐答案

你在这里做了最危险的错误，并且你认为删除时间部分将解决你的问题。

为了对你的 - 不好 - 解决方案，你的问题是你没有附上日期 - 带引号的时间，所以 idate 值不能被SQL解释...

但是！真正的问题是您使用字符串连接将值传递给SQL。这种技术打开你的系统进行SQL注入...

假设你将参数从用户发送到你的SQL。如果您的用户在用户名框中写下此内容将会发生什么：; DROP TABLE栏; -

您必须学习如何使用参数化查询 [ ^ ] ..

You are doing the most dangerous error here, and you think removing the time part will solve your problem.
To be true to your - bad - solution, the problem is that you do not enclose date-time with quotes, so idate value can not be interpreted by SQL...
BUT! The true problem is that you are using string concatenation to pass values to SQL. This technique opens your system to SQL injections...
Let say that you send parameters from a user to your SQL. What will happening if your user writes this in the username box: ;DROP TABLE bar;--
You have to learn how to use parameterized queries[^]..

cmd.CommandText="insert into tbl(id,idate) values(@id, @idate)";
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id
cmd.Parameters.Add("@idate", SqlDbType.DateTime).Value = idate
cmd.ExecuteNonQuery();

这就是我被卡住的地方。我无法使用参数化查询因为我执行查询的执行方法在不同的类中。我将查询作为字符串传递给不同类的方法。



如果你不能使用参数化查询，那么将来你可能会遇到更大，更大的问题：SQL注入不关心 为什么 它可以工作，它只是 - 而且可以让用户非常简单地破坏你的数据库 - 你应该强烈考虑重新编写类来使用参数化查询作为高优先级。



但你可以这样做：

"That's the place where I'm stuck. I can't use parameterized query as my Execute method for execution of query is in different class. I'm passing the query as string in method of different class."

If you can't use parameterised queries then you will probably have much, much bigger problems in future: SQL Injection doesn't care why it can work, it just does - and that can let users destroy your database very, very simply - you should strongly consider reworking the class to use parameterised queries as a high priority.

But you can do it:

string cmdText = "INSERT INTO tbl (id,idate) VALUES ('"+ id +"','"+ dtp.Value.ToString("yyyy-MM-dd") + "')";

但是任何字符串连接都是危险的，应该避免。谷歌的Bobby Tables，你会发现有趣的东西......

But any string concatenation is dangerous, and should be avoided. Google for "Bobby Tables" and you will find interesting stuff...

这篇关于如何从DateTime值中删除时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！