Need help with passing dates - SQL Server Forums

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)

Need help with passing dates

Author

Topic

swenri
Yak Posting Veteran

72 Posts

Posted - 2006-07-11 : 15:10:29

Hi all,

I have a question as to how to pass dates as parameters while executing a stored procedure in Sql server 2000.
My code for sp is as follows.

CREATE PROCEDURE sp_giftacknowledgement @giftdate1 datetime,@giftdate2 datetime
AS

Declare
@gifteffdate1 datetime,
@gifteffdate2 datetime

Select * from gifts_full where giftcamp
NOT LIKE '%star%' and giftcamp NOT LIKE '%web%'
and giftcamp NOT LIKE 'weba%'
and giftcamp NOT LIKE 'webn%'
and gifttype ='g' or gifttype = 'y'
and giftamount between 25 and 999.99
and gifteffdat between convert(varchar(10),'@gifteffdate1',101)
and convert(varchar(10),'@gifteffdate2',101)

when I pass as Execute sp_gi '19991231','20000418' Iam getting an error as Syntax error converting datetime from character string.

Thanks,

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-07-11 : 15:50:14

Try passing it in as 1999-12-31 00:00:00.000 see what happens....
--Nick

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-11 : 18:30:03

remove the single quote around your variable

and gifteffdat between convert(varchar(10),@gifteffdate1,101)
and convert(varchar(10),@gifteffdate2,101)

What is the data type of gifteffdat if is datetime, you don't have to convert to varchar at all.
Further more converting to varchar with sytle 101 gives you date string in format MM/DD/YYYY which is not suitable for performing comparison at all.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-12 : 02:16:09

http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-12 : 02:21:43

go to books online and search for the valid format for datetime

hope this helps...

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources