'<=' operator is not working in sql server 2000

Scenario is, database is in the maintenance phase. this database is not developed by ours developer. it is an existing database developed by the 'xyz' company in sql server 2000. This is real time database, where i am working now. I wanted to write the stored procedure which will retrieve me the records From date1 to date 2.so query is :

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

whereas my understanding I must get data including upper bound date as well as lower bound date. but somehow I am getting records from '3-May-2010' (which is fine but) to '10-Oct-2010'

As i observe in table design , for ColDate, developer had used 'varchar' to store the date. i know this is wrong remedy by them. so in my stored procedure I have also used varchar parameters as @FromDate1 and @ToDate to get inputs for SP. this is giving me result which i have explained. i tried to take the parameter type as 'Datetime' but it is showing error while saving/altering the stored procedure that "@FromDate1 has invalid datatype", same for "@ToDate".

situation is that, I can not change the table design at all. what i have to do here ? i know we can use user defined t开发者_如何转开发able in sql server 2008 , but there is version sql server 2000. which does not support the same. Please guide me for this scenario.

**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

but getting Error: Arithmetic overflow error converting expression to data type datetime. in sql server 2000 What should be that ? is i am wrong some where ? also

(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)

I can not understand what is going on ?

Can you CAST() or put a view on this table and convert all the dates to DateTime values? I can't remember if 2000 supported index views, but based on your varchar values (dd-mmm-yyyy), it's going to be a nightmare to try and select date rages (possible, by parsing but a pain) with those values.

You need to cast the "dates" in the varchar column to actual datetime values and then do your comparison. In addition, I would recommend using the format YYYYMMDD for your dates.

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

If you are passing parameters you would also want to force them to be datetime

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'

Of course, the best solution is to simply change the datatype in the table to datetime and correct the dependent programs or create a couple of computed columns which cast the varchar values to datetime or simply add a few static datetime columns and do a one-time update from the varchar columns.

Update

The error you are getting is because some of the varchar values cannot be converted into datetime (which is why having them as varchar is a bad idea). Either those values must be corrected or you have to exclude those values. To find values that are not dates, you can use the IsDate function.

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

Another way you can write your query to exclude those values would be:

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

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'

It sounds like you need to locate the bad data. I, too, have had to deal with what should have been datetime data stored in a varchar column. It can be difficult to track down the errant data. First, I opened the table and eyeballed it, looking for misformatted dates, but I couldn't find any, so I figured there were very few and that it would be quick to use a more programmatic approach. I ultimately used the divide-and-conquer approach: I divided the data in half and tried to cast each half to datetime. If either half caused an error, then i further divided it in half and checked those halves. It's a recursive process. I could have written T-SQL for this, but I did it kind of manually. It took maybe half an hour.

As an example, suppose MyTableName has a PRIMARY KEY named MyID with consecutive values starting at 1 and ending at 1000000. Then I would run two queries to check which half (or halves) contain invalid data:

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

If the first query caused an error, then I would run two more queries:

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

Suppose that in this iteration, only the second query caused an error. Then, I would run two more queries:

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

Eventually -- in log-base-2-of-n iterations or less -- you will find the first errant data value. Of course, you don't know how many errant values are in your table, so it could take time to find all of them. In my case, I had about 10 errant values, some of which were 'none' or 'NA' and which I UPDATEd to be NULL, and some of which were just misformatted dates, which I UPDATEd to be properly formatted dates. Having fixed the bad data, I was finally able to get back to the original task of analyzing the data.

Another way to find the bad data is to use SQL Server 2000's limited regular-expression functionality:

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

You'll have to complete the query yourself.