April 22, 2009 at 12:52 pm
I have the following code I created in server 2005 as a VIEW
select pledge.giftkey,pledge.gifteffdat,pledge.giftamount
from gift.pledge left outer join chart_of_accounts
on chart_code=pledg.giftrest
I am trying to create a stored procedure that allows me to enter in a date range (ie between 1/1/09 and 1/30/09). The paramter would reference the pledge.gifteffdat.
I assume i would do something like
Create Procedure usp_AddPledgeDate
@pledge.gifteffdat datetime
I want to show pledge.giftkey,pledge.gifteffdat,pledge.giftamount
from gift.pledge based on entering a date range which is generated by the parameter. How would I do this. I need the code
April 22, 2009 at 2:24 pm
Something like this should work...
CREATE PROCEDURE [dbo].[GIFTByDate] (@STARTDate datetime, @ENDDate datetime) AS
begin
select pledge.giftkey,pledge.gifteffdat,pledge.giftamount
from gift.pledge left outer join chart_of_accounts on chart_code=pledg.giftrest
where pledge.gifteffdat between @STARTDate and @ENDDate
end
For help on getting help, follow the link below:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 22, 2009 at 4:29 pm
Okay, there are a couple of things you should know. First, when checking dates and date ranges we need to strip the time portion from the input parameters to make sure we are searching for all possible times for a date. Next, we don't use between because it is inclusive and if we strip the time from the date - we would miss the end date.
For example:
WHERE mydate BETWEEN '20090101' AND '20090101'
The above will only match where the column 'mydate' was exactly '20090101 00:00:00.000'. If 'mydate' was '20090101 00:00:00.333' - it would not be included because the date criteria that we used for the end date is implicitly converted to '20090101 00:00:00.000'.
So, we use less than/greater than and add one date to our end date (stripped of the time). So, we end up with:
WHERE mydate >= '20090101' -- implicitly converted to '20090101 00:00:00.000'
AND mydate = @STARTDate
AND p.gifteffdat < DATEADD(day, 1, @ENDDate) -- add one day to include our passed in end date
GO
[/code]
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 23, 2009 at 6:09 am
CREATE PROCEDURE dbo.GIFTByDate
@STARTDate datetime
,@ENDDate datetime
AS
SET @StartDate = DATEADD(day, DATEDIFF(day, 0, @StartDate), 0); -- get start date with 00:00:00.000 time
SET @EndDate = DATEADD(day, DATEDIFF(day, 0, @EndDate), 0); -- get end date with 00:00:00.000 time
SELECT p.giftkey
,p.gifteffdat
,p.giftamount
FROM dbo.pledge
LEFT JOIN chart_of_accounts on chart_code = p.giftrest
WHERE p.gifteffdat >= @STARTDate
AND p.gifteffdat < DATEADD(day, 1, @ENDDate) -- add one day to include our passed in end date
GO
For help on getting help, follow the link below:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 23, 2009 at 6:13 am
Sorry about previous post. Clicked wrong button.
Jeffrey, you are correct with regard to timestamp issue. I hurried past that without thinking about the single day selection.
I bow to your greatness...:hehe:
For help on getting help, follow the link below:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply