November 30, 2009 at 4:25 am
I have created a stored procedure with
ALTER PROCEDURE [dbo].[myStoredProcedurel]
(
@StartDate AS DateTime
, @EndDate AS DateTime
)
AS
BEGIN
......... WHERE
(myDateField BETWEEN @StartDate AND @EndDate)
When this report is run how do I select the previous 3 whole months Start and End Date,
So for example if i run this Stored Procedure today, the StartDate should be 01/08/2009 and the EndDate is 31/10/2009.
and if i ran this on the 01/12/2009, the StartDate should be 01/09/2009 and the EndDate is 30/11/2009
I'm guessing my WHERE clause need alteration
November 30, 2009 at 4:50 am
Did you try this:
ALTER PROCEDURE [dbo].[myStoredProcedurel]
(
@StartDate AS DateTime = NULL
, @EndDate AS DateTime = NULL
)
AS
BEGIN
declare @refStartDate datetime
declare @refEndDate datetime
Set @refStartDate = isnull(@StartDate, dateadd(ms, -3, dateadd(mm, datediff(mm, 0, getdate()), 0)))
Set @refEndDate = isnull( @refEndDate, dateadd(mm, -3, isnull(@StartDate, dateadd(mm, datediff(mm, 0, getdate()), 0)) )) -- start date - 3 months
......... WHERE
(myDateField BETWEEN @refStartDate AND @refEndDate )
...
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 30, 2009 at 9:05 am
DATEADD is the way to pick the dates based on today, as ALZDBA has mentioned. You can read more about it here:
http://msdn.microsoft.com/en-us/library/ms186819%28SQL.90%29.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply