将 Varchar 数据类型转换为日期时间导致超出范围

发布于 2024-11-18 16:11:50 字数 624 浏览 3 评论 0原文

我有问题。我无法识别我的错误...

int dt = Convert.ToInt32(Items.Rows[T1]["F14"].ToString().Trim());
int mn = Convert.ToInt32(Items.Rows[T1]["F15"].ToString().Trim());
int yr = Convert.ToInt32(Items.Rows[T1]["F16"].ToString().Trim());
string DtString = mn.ToString().Trim() + "/" + dt.ToString().Trim() + "/" + yr.ToString().Trim();
DateTime RegExp = Convert.ToDateTime(DtString);

exp_date is datetime field in sqlserver.

string MyDtQry = "UPDATE MyTable SET exp_date='" + RegExp + "' where MyTable.id_no='" + AlmIDNo + "'";

但我收到错误：

将 Varchar 数据类型转换为日期时间结果超出范围

原文

I have problem. I can' identify my mistake...

int dt = Convert.ToInt32(Items.Rows[T1]["F14"].ToString().Trim());
int mn = Convert.ToInt32(Items.Rows[T1]["F15"].ToString().Trim());
int yr = Convert.ToInt32(Items.Rows[T1]["F16"].ToString().Trim());
string DtString = mn.ToString().Trim() + "/" + dt.ToString().Trim() + "/" + yr.ToString().Trim();
DateTime RegExp = Convert.ToDateTime(DtString);

exp_date is datetime field in sqlserver.

string MyDtQry = "UPDATE MyTable SET exp_date='" + RegExp + "' where MyTable.id_no='" + AlmIDNo + "'";

But I'am getting the error:

Conversion of a Varchar Datatype to a datetime resultant in an out of range

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

暖伴 2024-11-25 16:11:51

好吧，我会以非常不同的方式处理这个任务：

在将日、月和年作为整数获取之后，我绝对不会将它们重新组合在一起并解析它们。只需使用：
```
// 注意这里有意义的变量名称，顺便说一句...
DateTime 日期 = new DateTime(年、月、日);
```
更新数据库时，我不会将值直接放入SQL 语句中。请改用参数化 SQL 语句，并将参数设置为date。这样，您就不必担心数据库需要与您提供的日期格式不同的日期格式。一般来说，您应该始终使用参数化 SQL，而不是将值直接嵌入到 SQL 中 - 以及帮助解决这种情况，它可以避免 SQL 注入攻击。

现在，在完成所有这些操作之后，如果您仍然收到错误，则应该检查您尝试插入的实际数据。也许Items中的数据确实超出了SQL Server的范围。

Well, I would approach the task very differently:

After getting the day, month and year as integers I definitely wouldn't stick them back together and parse them. Just use:
```
// Note the meaningful variable names here, btw...
DateTime date = new DateTime(year, month, day);
```
When updating the database, I wouldn't put the value directly into the SQL statement. Use a parameterized SQL statement instead, and set the parameter to date. That way you don't need to worry about the database expecting a different date format to the one you provide. In general, you should always use parameterized SQL rather than embedding the values directly into the SQL - as well as helping with this kind of situation, it avoids SQL injection attacks.

Now, after doing all of that, if you're still getting an error, you should check what actual data you're trying to insert. Maybe the data in Items really is out of range for SQL Server.

回复收藏 0 原文

遥远的她 2024-11-25 16:11:51

由于您没有显示任何输入，所以很难准确地看到；然而，以下内容显然是危险的：

DateTime RegExp = Convert.ToDateTime(DtString);

string MyDtQry = "UPDATE MyTable SET exp_date='" + RegExp + "' where MyTable.id_no='" + AlmIDNo + "'";

即使我们掩盖了您应该使用参数（您确实应该）的事实，您也需要以 SQL Server 期望的方式格式化此日期 - 这可能与本地的非常不同格式。

然而;不用麻烦格式化它！为此使用一个参数，它就会消失。这并不难 - 例如使用 dapper：

DateTime RegExp = new DateTime(yr, mn, dt);

connection.Execute("UPDATE MyTable SET exp_date=@exp where MyTable.id_no=@id",
    new { exp = RegExp, id = AlmIDNo });

就完成了;完全安全，免受注入和（在这种情况下更有可能）将数据格式化为字符串的问题。

It is hard to see exactly since you don't show any of your inputs; however, the following is clearly dangerous:

DateTime RegExp = Convert.ToDateTime(DtString);

string MyDtQry = "UPDATE MyTable SET exp_date='" + RegExp + "' where MyTable.id_no='" + AlmIDNo + "'";

Even if we gloss over the fact that you should be using parameters (you really should), you would need to format this date in the way that SQL server expects - which could be very different to the local format.

However; don't bother formatting it! Use a parameter for this, and it'll go away. This doesn't need to be hard - for example with dapper:

DateTime RegExp = new DateTime(yr, mn, dt);

connection.Execute("UPDATE MyTable SET exp_date=@exp where MyTable.id_no=@id",
    new { exp = RegExp, id = AlmIDNo });

and done; fully safe from both injection and the (more likely in this case) issue of formatting data as strings.

回复收藏 0 原文