Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Nth occurence of a day in a month/year Expand / Collapse
Author
Message
Posted Thursday, August 13, 2009 4:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 10, 2012 4:37 AM
Points: 16, Visits: 30
Comments posted to this topic are about the item Nth occurence of a day in a month/year
Post #770564
Posted Friday, August 21, 2009 7:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 13, 2011 8:06 AM
Points: 2, Visits: 20
Can't get this to work.
Post #775056
Posted Friday, August 21, 2009 11:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 1,297, Visits: 1,659
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
)
)

Post #775298
Posted Friday, August 21, 2009 9:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 10, 2012 4:37 AM
Points: 16, Visits: 30
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 :)
Post #775498
Posted Saturday, August 22, 2009 2:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:06 PM
Points: 36,786, Visits: 31,243
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #775599
Posted Saturday, August 22, 2009 2:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:06 PM
Points: 36,786, Visits: 31,243
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #775602
Posted Monday, August 24, 2009 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 13, 2011 8:06 AM
Points: 2, Visits: 20
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



Post #776096
Posted Monday, August 24, 2009 10:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:06 PM
Points: 36,786, Visits: 31,243
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #776149
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse