在sql和asp中超出范围问题 [英] out of range problem in sql and asp

问题描述

我有一个数据库，我以DD / MM / YYYY格式保存日期。
当我在我的应用程序中运行查询时


< blockquote class =quote>

Quote：

SelectCommand =SELECT * FROM [numbers] ORDER BY CONVERT（DateTime，Date_，103）DESC

i超出范围错误

我的数据表有列像（Date_ varchar（200），number varchar（200））

但我有类似的具有相似列的表格，表格名称是数字

，当我运行相同的查询时

Quote：

SelectCommand =SELECT * FROM [number] ORDER BY CONVERT（DateTime，Date_，103）DESC

i我得到了预期的结果。

所以告诉我第一次查询有什么问题。

我还有一个问题

i希望在网格视图中逐月显示结果。

那么表格格式是什么样的，这样我就可以了可以在其中输入日期数据。

喜欢

2015年1月1日10 1/2/2015 20 1/3/2015 50 ...依此类推1 / 12/2015 40

2/1/2015 15 2/2/2015 25 2/3/2015 55 ...依此类推2015年2月12日67

3/1/2015 20 3/2/2015 30 3/3/2015 60 ...依此类推2015年3月12日76

这里结果必须是最近三个月

假设最新月份是12月然后只有10月，11月，12月

txs提前

解决方案

更改你的数据库。



永远不要将日期值存储为NVARCHAR - 总是存储而不是DATETIME：这样你就不必动态转换到DateTime，并且你没有得到超出范围的错误，因为你的表中的数据总是有效的。



目前，你的一个或多个strin gs不正确，当SQL尝试将其转换为DateTime值时，它会失败，并抛出异常。这将继续，直到您找到无效的条目或条目并修复它们。但这只是一个临时解决方案 - 因为如果你的代码中有一个错误的日期，它将来会有更多，所以这个问题将继续发生，直到你改变你的数据库。现在更改它现在更容易，而它仍在开发中，它正处于生产阶段......

i have a data base where i am saving the date in DD/MM/YYYY format.
when i am running a query in my application which is

Quote:

SelectCommand="SELECT * FROM [numbers] ORDER BY CONVERT(DateTime, Date_, 103) DESC"

i am getting out of range error
my data table has column like (Date_ varchar(200),number varchar(200))
but i have the similar table with similar column the table name is number
and when i m running the same query

Quote:

SelectCommand="SELECT * FROM [number] ORDER BY CONVERT(DateTime, Date_, 103) DESC"

i am getting the desired result.
so tell me what is wrong in first query.
and one more question i have
i want to display results month wise in grid view.
so what will be possible table format so that i can enter date wise data in it.
like
1/1/2015 10 1/2/2015 20 1/3/2015 50 ... and so on 1/12/2015 40
2/1/2015 15 2/2/2015 25 2/3/2015 55 ...and so on 2/12/2015 67
3/1/2015 20 3/2/2015 30 3/3/2015 60 ...and so on 3/12/2015 76
here the result had to be just for last three months
suppose if newest month is December then only October,November,December
txs in advance

解决方案

Change your DB.

Never store date values as NVARCHAR - always store than as DATETIME instead: that way you don't have to do any conversion to DateTime on the fly, and you don;t get "out-of-range" errors because the data in your table is always valid.

At the moment, one or more of your strings is not correct, and when SQL tries to convert it to a DateTime value, it fails, and throws the exception. And that will continue until you find the invalid entry or entries and fix them. But that will only be a temporary solution - because if your code has let one bad date in, it will let more in the future, so this problem will continue to occur until you do change your DB. And it's a lot easier to change it now while it's still in development that it is when it's in production...

这篇关于在sql和asp中超出范围问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！