将varchar数据类型转换为日期时间数据类型会导致超出范围的值。 [英] The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

问题描述

我收到

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

错误

在运行sql查询时为

从表中选择*，其中[Date] = CONVERT（VARCHAR，'+ DateTime.Now.Date.ToShortDateString（）+'，111）

因为111适用于yyyy / mm / dd格式

103适用于dd / mm / yyyy格式

101适用于mm / dd / yyyy格式



但仍有时工作，有时不工作。就像在我当地的那样工作正常，因为我相应地设置了系统的时间，但是当部署在服务器上时，它无法正常工作。是否有完整的解决方案，如何在查询中使用任何格式的日期？



谢谢



我的尝试：



我已经使用了所有格式，即

因为111是yyyy / mm / dd mat 
103用于dd / mm / yyyy格式
101用于mm / dd / yyyy格式

转换时间但是它可以在某个平台上运行，有些不是

解决方案

简单：从不连接字符串以形成SQL命令 - 它非常危险，并且导致转换问题如此。始终使用参数化查询，并直接传递DateTime值而不进行转换。您的代码将不再被SQL注入攻击，并且您的问题将在同一时间消失。

首先，您需要检查 DateTime的值.Now.Date.ToShortDateString（），有时可能会有惊喜。

答案取决于计算机文化设置。

使用调试器在程序运行时查看值。

-----

你应该学会尽快使用调试器。而不是猜测你的代码在做什么，现在是时候看到你的代码执行并确保它完成你期望的。



调试器允许你跟踪执行逐行检查变量，你会看到它有一个停止做你期望的点。

调试器 - 维基百科，免费的百科全书 [ ^ ]

掌握Visual Studio 2010中的调试 - A初学者指南 [ ^ ]



调试器在这里向您展示您的代码正在做什么，您的任务是与它应该做什么进行比较。 />
调试器中没有魔法，它没有发现错误，它只是帮助你。当代码没有达到预期的效果时，你就接近了一个bug。

-----

引用：

从表中选择*，其中[Date] = CONVERT（VARCHAR，'+ DateTime.Now.Date.ToShortDateString（）+'，111）

从不这样做，因为它提升了用户对SQL命令的输入，并且可能导致称为SQL注入的问题。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

SQL注入的解决方案也将解决您的问题，因为sql server将知道您的日期是一个日期。

Hi,

I'm getting "

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

" error
while running the sql query as "

Select * from table where [Date]=CONVERT(VARCHAR,'" + DateTime.Now.Date.ToShortDateString() + "',111)"

"

As "111" is for yyyy/mm/dd format
"103" is for dd/mm/yyyy format
"101" is for mm/dd/yyyy format

but still sometimes work and sometimes not. As on my local it is working properly as I set the time of my system accordingly but when deployed on server it is not working. Is there a complete solution, how to use date in the query as in any format?

Thanks

What I have tried:

I've used all the format i.e

As "111" is for yyyy/mm/dd format
"103" is for dd/mm/yyyy format
"101" is for mm/dd/yyyy format

to convert the time but it works in some platform and some not

解决方案

Simple: never concatenate strings to form an SQL command- it's very dangerous, as well as causing conversion problems such as this. Always use parameterized queries instead, and pass the DateTime value directly without conversion. Your code will no longer be print took SQL Injection attacks, and your problem will disappear the same time.

First of all, you need to check the value of DateTime.Now.Date.ToShortDateString(), you may get a surprise, sometimes.
The answer depend on computer culture settings.
Use the debugger to see the value as the program runs.
-----
You should learn to use the debugger as soon as possible. Rather than guessing what your code is doing, It is time to see your code executing and ensuring that it does what you expect.

The debugger allow you to follow the execution line by line, inspect variables and you will see that there is a point where it stop doing what you expect.
Debugger - Wikipedia, the free encyclopedia[^]
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]

The debugger is here to show you what your code is doing and your task is to compare with what it should do.
There is no magic in the debugger, it don't find bugs, it just help you to. When the code don't do what is expected, you are close to a bug.
-----

Quote:

Select * from table where [Date]=CONVERT(VARCHAR,'" + DateTime.Now.Date.ToShortDateString() + "',111)"

Never do this, because it promote a user input to SQL command and can lead to a problem called SQL Injection.
SQL injection - Wikipedia[^]
SQL Injection[^]
The solution to SQL injection will also solve your problem as sql server will know that your date is a date.

这篇关于将varchar数据类型转换为日期时间数据类型会导致超出范围的值。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！