August 13, 2009 at 4:54 pm
Comments posted to this topic are about the item Nth occurence of a day in a month/year
August 21, 2009 at 7:40 am
Can't get this to work.
August 21, 2009 at 11:52 am
Why not just do (using the function definition and parameters (and the @firstDate variable) as given):
set @firstDate = dateadd(dd,1-datepart(dd,@inputdate) ,@inputDate) -- ensures that we start with the first of the month
select
dateadd(wk, @occurAt - 1, -- adds the additional weeks, if any
dateadd(dd, @day - datepart(dw, @firstDate)-- moves to the given day of week
+ case when @day < datepart(dw, dateadd(dd,1-datepart(dd,@firstdate) ,@firstDate)) then 7 else 0 end, -- ensures we don't go back to the last month
@firstDate
)
)
August 21, 2009 at 9:36 pm
Can't get this to work.
How r u setting the parameter? Let me explain it.
For example if you need date for 3rd Sunday of March 2009 then use as follows
SELECT dbo.getDateAtNthOccurence('01/Mar/2009',1,3)
The output will be 2009-03-15 (15th march 2009)
The first parameter is any date of month/year for which we want the occurrence date.
The second parameter is as follows (if you need sunday then send 1 as parameter value):
1:Sunday, 2:Monday, 3:Tuesday, 4:Wednesday, 5:Thrusday, 6:Friday, 7:Saturday
The third parameter is as follows (if you need 3rd sunday then send 3 as parameter value):
1:First, 2:Second, 3:Third, 4:Fourth, >4:Last
hope this will help you to get the output.
cheers 🙂
August 22, 2009 at 2:00 pm
michelle.baumgarten (8/21/2009)
Can't get this to work.
The code works fine for me. You need to post the exact code that you can't get to work so we can help.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 22, 2009 at 2:09 pm
sknox (8/21/2009)
Why not just do (using the function definition and parameters (and the @firstDate variable) as given):
set @firstDate = dateadd(dd,1-datepart(dd,@inputdate) ,@inputDate) -- ensures that we start with the first of the month
select
dateadd(wk, @occurAt - 1, -- adds the additional weeks, if any
dateadd(dd, @day - datepart(dw, @firstDate)-- moves to the given day of week
+ case when @day < datepart(dw, dateadd(dd,1-datepart(dd,@firstdate) ,@firstDate)) then 7 else 0 end, -- ensures we don't go back to the last month
@firstDate
)
)
I agree... the only desireable thing missing from the above code is for the "LAST" desired occurance. For example... for 2012-02-01 looking for Sunday(1) as the last occurance of a Sunday in the month (5), your good code returns the first Sunday of March instead of the last Sunday of February.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2009 at 9:23 am
Since you asked --- I copied the code, entered '01/MAR/2009',1,3 for the parameters, and got a message saying:
Incorrect syntax near keyword FUNCTION.
A RETURN statement with a RETURN value cannot be used in this context.
CREATE FUNCTION dbo.getDateAtNthOccurence
(
@inputDate DATETIME,
@day TINYINT, -- 1:Sunday, 2:Monday, 3:Tuesday, 4:Wednesday, 5:Thrusday, 6:Friday, 7:Saturday
@occurAt TINYINT -- 1:First, 2:Second, 3:Third, 4:Fourth, >4:Last
)
RETURNS DATETIME
AS
BEGIN
DECLARE @counter TINYINT,
@firstDate DATETIME,
@lastDate DATETIME,
@retDate DATETIME
SELECT @counter = 1, @firstDate = DATEADD(dd,-(DAY(@inputDate)-1),@inputDate), @lastDate = DATEADD(dd,-1,DATEADD(MONTH,1,@firstDate))
WHILE @firstDate<=@lastDate
BEGIN
IF(DATEPart(dw , @firstDate )=@day) and @counter<=@occurAt
BEGIN
SELECT @retDate = @firstDate,@counter = @counter+1
END
SET @firstDate = DATEADD(DAY,1,@firstDate)
END
-- Return the result of the function
RETURN @retDate
END
GO
August 24, 2009 at 10:26 am
michelle.baumgarten (8/24/2009)
Since you asked --- I copied the code, entered '01/MAR/2009',1,3 for the parameters, and got a message saying:Incorrect syntax near keyword FUNCTION.
A RETURN statement with a RETURN value cannot be used in this context.
CREATE FUNCTION dbo.getDateAtNthOccurence
(
@inputDate DATETIME,
@day TINYINT, -- 1:Sunday, 2:Monday, 3:Tuesday, 4:Wednesday, 5:Thrusday, 6:Friday, 7:Saturday
@occurAt TINYINT -- 1:First, 2:Second, 3:Third, 4:Fourth, >4:Last
)
RETURNS DATETIME
AS
BEGIN
DECLARE @counter TINYINT,
@firstDate DATETIME,
@lastDate DATETIME,
@retDate DATETIME
SELECT @counter = 1, @firstDate = DATEADD(dd,-(DAY(@inputDate)-1),@inputDate), @lastDate = DATEADD(dd,-1,DATEADD(MONTH,1,@firstDate))
WHILE @firstDate<=@lastDate
BEGIN
IF(DATEPart(dw , @firstDate )=@day) and @counter<=@occurAt
BEGIN
SELECT @retDate = @firstDate,@counter = @counter+1
END
SET @firstDate = DATEADD(DAY,1,@firstDate)
END
-- Return the result of the function
RETURN @retDate
END
GO
I suspect that you're changing the code above and then trying to run it as if it were a script. It will not work that way. Run the code as it is above to create the function and then run something like the following to use the function...
SELECT dbo.getDateAtNthOccurence(GETDATE(),1,5)
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply