Stored Procedure datetime error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure datetime error

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-12-27 : 23:06:20

Guys.

I am getting the error when I am trying to pass datetime values for the stored procedure.

Can someone suggest me if there is any work around for this store procedure to work if DATETIME values are passed as input parameters.

alter procedure spExport (@date1 datetime, @date2 datetime) as
set nocount on
begin
select * from employee where daterecorded between @date1 and @date2
select * from person where id in (select empid from employee daterecorded between @date1 and @date2)
end
set nocount off

execute spExport '2006-11-01 00:00:00.000', '2006-11-31 23:59:59.000'

Server: Msg 8114, Level 16, State 4, Procedure spExport, Line 0
Error converting data type varchar to datetime.

jwize
Starting Member

7 Posts

Posted - 2006-12-28 : 02:05:36

Seem like you should just cast those values to datetimes first. I am new to sql.
execute spExport CAST('2006-11-01 00:00:00.000' AS DATETIME), CAST('2006-11-31 23:59:59.000' AS DATETIME);

Just a thought.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-28 : 02:58:19

What is the data type of daterecorded column? It seems to be varchar.

Try this:

select * from employee
where Convert(Datetime, daterecorded) between @date1 and @date2

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

srinath
Starting Member

16 Posts

Posted - 2006-12-28 : 04:05:14

There is no 31st date in The Month on Novemeber........

execute spExport '2006-11-01 00:00:00.000', '2006-11-30 23:59:59.000'

see using the above again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-01 : 03:40:52

quote:
Originally posted by srinath

There is no 31st date in The Month on Novemeber........

execute spExport '2006-11-01 00:00:00.000', '2006-11-30 23:59:59.000'

see using the above again.

Good Catch srinath

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-01 : 03:54:21

quote:
Originally posted by jwize

Seem like you should just cast those values to datetimes first. I am new to sql.
execute spExport CAST('2006-11-01 00:00:00.000' AS DATETIME), CAST('2006-11-31 23:59:59.000' AS DATETIME);

Just a thought.

No. You cant directly use CAST function when passing it as paramter
You need to define a variable and pass
Declare @date datetime
SELECT @date =CAST(datetstring as DATETIME)
EXEC Proc @date

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources