Date Range Parameter Question in a Stored Procedur

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 2005 Forums

Transact-SQL (2005)

Date Range Parameter Question in a Stored Procedur

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2009-09-14 : 09:45:56

Here is my code:
Alter Procedure NGP Balance
@GiftEffectiveDate datetime

as
Select giftkey,giftid,account
from
gift_table
where gifteffectiveDate = @GiftEffectiveDate
and gifttype = 'p'Union All
Select giftkey,giftid,account
from
gift_table
where gifteffectiveDate = @GiftEffectiveDate
and gifttype = 'c'

1. I do a UnionAll because we are pulling two different sets of gifttype
2. Is there a way to make the parameter a date range parameter?
3. Since I am pulling in two instances of the git_table, must I also have two instances of the @GiftEffectiveDate and must they be named differently?
4. How can I format the @GiftEffectiveDate so when inputted, I can type in mmyydddd. The dattime forces you to type in mmddyyyy 00:00:00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-14 : 09:48:32


Alter Procedure NGP Balance
@GiftEffectiveDate_s datetime,
@GiftEffectiveDate_e datetime
as

Select 	giftkey,giftid,account
from	gift_table
where 	gifteffectiveDate >= @GiftEffectiveDate_s
and	gifteffectiveDate <= @GiftEffectiveDate_e
and 	gifttype in ( 'p', 'c' )

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-14 : 09:51:11

What did you mean by date range?

Madhivanan

Failing to plan is Planning to fail

Dennis Falls
Starting Member

41 Posts

Posted - 2009-09-14 : 15:29:49

You can pass your parameter as a varchar and it will work fine.

@GiftEffectiveDate_s = '9/14/2009',
@GiftEffectiveDate_e = '9/14/2009'

Also, I would suggest using

AND gifteffectiveDate < dateadd(Day,1,@GiftEffectiveDate_e)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources