Hello,
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 table 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 ?
