'<='运算符在 sql server 2000 中不起作用

Question

场景是，数据库处于维护阶段。该数据库不是由我们的开发人员开发的。它是'xyz'公司在sql server 2000中开发的现有数据库。这是我现在工作的实时数据库。我想编写一个存储过程，它将检索从 date1 到 date 2 的记录。所以查询是：

Select * from MyTableName
Where colDate>= '3-May-2010' and colDate<= '5-Oct-2010' and colName='xyzName'

而我的理解是，我必须获取包括上限日期和下限日期的数据。但不知何故，我得到了从“2010 年 5 月 3 日”（这很好，但是）到“2010 年 10 月 10 日”的记录，

正如我在表设计中观察到的那样，对于 ColDate，开发人员使用“varchar”来存储日期。我知道他们这是错误的补救措施。因此，在我的存储过程中，我还使用了 varchar 参数作为 @FromDate1 和 @ToDate 来获取 SP 的输入。这给了我我已经解释过的结果。我尝试将参数类型设为“Datetime”，但在保存/更改存储过程时显示错误“@FromDate1 具有无效数据类型”，与“@ToDate”相同。

情况是，我根本无法改变表格设计。我必须在这里做什么？我知道我们可以在sql server 2008中使用用户定义的表，但是有版本sql server 2000。它不支持相同的功能。 请指导我应对这种情况。

**Edited**

I am trying to write like this SP:

    CREATE PROCEDURE USP_Data  
    (@Location varchar(100),
    @FromDate  DATETIME,
    @ToDate DATETIME) AS

    SELECT     *
    FROM         dbo.TableName
    Where   CAST(Dt  AS DATETIME) >=@fromDate and  CAST(Dt  AS  DATETIME)<=@ToDate  and Location=@Location
    GO

但出现错误：将表达式转换为数据类型日期时间时出现算术溢出错误。在 SQL Server 2000 中 那应该是什么？我是不是哪里错了？ 我也

(202 row(s) affected) is changes every time in circular manner means first time sayin 
(122 row(s) affected)
run again saying 
(80 row(s) affected)
if again 
(202 row(s) affected)
if again 
(122 row(s) affected)

无法理解发生了什么事？

Answer 1

您可以 CAST() 或在此表上放置一个视图并将所有日期转换为 DateTime 值吗？我不记得 2000 是否支持索引视图，但是根据您的 varchar 值（dd-mmm-yyyy），尝试使用这些值选择日期范围（可能，但解析很痛苦）将是一场噩梦。

Answer 2

您需要将 varchar 列中的“日期”转换为实际的 datetime 值，然后进行比较。此外，我建议您的日期使用 YYYYMMDD 格式。

Select ...
From MyTableName
Where Cast(colDate As datetime) >= '20100503'
    And Cast(colDate As datetime) <= '20101005'
    And colName = 'xyzName'

如果您传递参数，您还希望强制它们为日期时间。

Create Procedure Foo
    @FromDate datetime
    , @ToDate datetime
    ...
As
Select ...
From MyTableName
Where Cast(colDate As datetime) >= @FromDate
    And Cast(colDate As datetime) <= @ToDate
    And colName = 'xyzName'

当然，最好的解决方案是简单地将表中的数据类型更改为日期时间并更正相关程序或创建几个计算将 varchar 值转换为 datetime 的列，或者简单地添加一些静态 datetime 列并从 varchar 列进行一次性更新。

更新

您收到的错误是因为某些 varchar 值无法转换为 datetime （这就是为什么将它们作为 varchar 是一个坏主意）。要么必须更正这些值，要么必须排除这些值。要查找非日期值，您可以使用 IsDate 函数。

Select ...
From MyTableName
Where IsDate( colDate ) = 0

您可以编写查询来排除这些值的另一种方法是：

Select ...
From    (
        Select Cast(colDate As datetime) As CastedDate
            , ...
        From MyTable
        Where IsDate( colDate ) = 1
        )
Where CastedDate >= @FromDate
    And CastedDate <= @ToDate

Answer 3

SELECT
    *
FROM
    MyTableName
WHERE
    CAST(colDate AS DATETIME) >= '3-May-2010'
        and
    CAST(colDate AS DATETIME) < DATEADD(day, 1, '5-Oct-2010')
        AND
    colName = 'xyzName'

Answer 4

听起来您需要找到坏数据。我也必须处理应该存储在 varchar 列中的日期时间数据。追踪错误数据可能很困难。首先，我打开表格并观察它，寻找格式错误的日期，但我找不到任何日期，所以我认为日期很少，并且使用更具编程性的方法会很快。我最终使用了分而治之的方法：我将数据分成两半，并尝试将每一半转换为日期时间。如果任何一半导致错误，那么我将其进一步分成两半并检查这两半。这是一个递归过程。我本可以为此编写 T-SQL，但我是手动完成的。大概花了半个小时。

例如，假设 MyTableName 有一个名为 MyID 的主键，其连续值从 1 开始到 1000000 结束。然后我将运行两个查询来检查哪一半（或哪一半）包含无效数据：

SELECT CAST(colDate AS DATETIME) WHERE MyID BETWEEN 1 AND 500000
GO
SELECT CAST(colDate AS DATETIME) WHERE MyID BETWEEN 500001 AND 1000000
GO

如果第一个查询导致错误，则我将再运行两个查询：

SELECT CAST(colDate AS DATETIME) WHERE MyID BETWEEN 1 AND 250000
GO
SELECT CAST(colDate AS DATETIME) WHERE MyID BETWEEN 250001 AND 500000
GO

假设在本次迭代中，只有第二个查询导致错误。然后，我将再运行两个查询：

SELECT CAST(colDate AS DATETIME) WHERE MyID BETWEEN 250001 AND 375000
GO
SELECT CAST(colDate AS DATETIME) WHERE MyID BETWEEN 375001 AND 500000
GO

最终，在 log-base-2-of-n 迭代或更少的时间内，您将找到第一个错误的数据值。当然，您不知道表中有多少错误值，因此可能需要一些时间才能找到所有错误值。就我而言，我有大约 10 个错误值，其中一些是“none”或“NA”，我将其更新为 NULL，其中一些只是格式错误的日期，我将其更新为格式正确的日期。修复了错误的数据后，我终于能够回到分析数据的最初任务了。

Answer 5

查找不良数据的另一种方法是使用 SQL Server 2000 有限的正则表达式功能：

SELECT
  *
FROM
  MyTableName
WHERE
  colDate NOT LIKE '[1-9]-Jan-[12][0-9][0-9][0-9]'
    AND
  colDate NOT LIKE '[12][0-9]-Jan-[12][0-9][0-9][0-9]'
    AND
  colDate NOT LIKE '3[01]-Jan-[12][0-9][0-9][0-9]'
    AND
  colDate NOT LIKE '[1-9]-Feb-[12][0-9][0-9][0-9]'
    AND
  colDate NOT LIKE '1[0-9]-Feb-[12][0-9][0-9][0-9]'
    AND
  colDate NOT LIKE '2[0-8]-Feb-[12][0-9][0-9][0-9]' -- ignore Feb 29 for now
    AND

您必须自己完成查询。