Datetime and Union in Stored Procedure (SQL 2005)

[vcujackson]

I have the following code to be used in a stored procedure to ask for a datetime @Giftdate (ie 2009-06-07 00:00:00).
I am doing a union on the same table (gifttable) I am using an alias as you can see here. My question is
1. Can I declare the two @Giftdates as I have done here to tell them apart
2. Based on the alias set up, should I be able to save this as a stored procedure and run this.
3. Have I formatted the datetime correctly

Create Proceedure NGP_Balance_Parameter
@Giftdateg --gift
@Giftdatep --pledge

SELECT g.giftid,g.giftdate
from
gifttable g
where g.giftdate >=@Giftdateg and
g.giftdate<=@Giftdateg
and g.gifttype = ('g')

union
SELECT p.giftid,p.giftdate
from
gifttable p
where p.giftdate >=@Giftdatep
and p.giftdate <=@Giftdatep
and p.gifttype = ('p')

 

[r937]

> 1. Can I declare the two @Giftdates as I have done here to tell them apart

yes, but if you're going to feed exactly the same datetime value to them, you don't need to

> 2. Based on the alias set up, should I be able to save this as a stored procedure and run this.

yes, but the aliases are somewhat superfluous in this query

in fact, the result set will consist exactly two columns, giftid and giftdate, and there is no way for you to tell which table each row came from

> 3. Have I formatted the datetime correctly

you haven't formatted it at all here

by the way, your use of UNION here means that if there happens to be a gift with the same id and same amount as a pledge, only one of them will be included in the result set -- you should use UNION ALL instead

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

The code you posted doesn't make much sense.

Try instead

Create Procedure NGP_Balance_Parameter
@GiftDate datetime
   
   SELECT g.giftid,g.giftdate from
     gifttable where giftdate >=DATEADD(day, DATEDIFF(day, '20040101', @GiftDate), '20040101')
     and g.giftdate< DATEADD(day, 1 + DATEDIFF(day, '20040101', @GiftDate), '20040101')
     and g.gifttype IN ('g','p')