Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Every 3rd Friday of the Month Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, February 17, 2011 12:33 PM
 Forum Newbie Group: General Forum Members Last Login: Thursday, February 17, 2011 12:31 PM Points: 4, Visits: 18
 I'm trying to find every 3rd friday of the month The Following code retruns for the current monthSELECT CONVERT(CHAR(10),DATEADD(wk,2,(CURRENT_TIMESTAMP -Day(CURRENT_TIMESTAMP)+1)+(6-DATEPART(dw,(CURRENT_TIMESTAMP -Day(CURRENT_TIMESTAMP)+1)))),121) if the current day is passed the 3rd friday of the month , I need to show them the next month's 3rd Friday dateThanks
Post #1065933
 Posted Thursday, February 17, 2011 1:13 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 1:49 PM Points: 6,703, Visits: 11,733
 What does your requirement say if the day passed is between the third Saturday of the month and the last day of the month?Ooops, misread your comment "current day passed is" vs "current day is passed". __________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --PlatoBelieve you can and you're halfway there. --Theodore RooseveltEverything Should Be Made as Simple as Possible, But Not Simpler --Albert EinsteinThe significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1065959
 Posted Thursday, February 17, 2011 1:17 PM
 SSC Rookie Group: General Forum Members Last Login: Yesterday @ 11:51 AM Points: 30, Visits: 248
 You should be able to accomplish what you want by using a CASE statement. Psuedocode as follows.SELECT CASE WHEN current_timestamp > (your 3rd FRIDAY of month calculation)THEN (altered 3rd FRIDAY of a month calculation based on DATEADD(MONTH, +1, currenttimestamp) instead of current_timestampELSE (your current 3rd FRIDAY of the month calculation)ENDBe careful when you use DW in a datepart function as the number that represents a particular day will change based on the value of @@DATEFIRST (Depending on your locale the ordinal position of a day in a week may be different than what you expect). Check books online for SET DATEFIRST and @@DATEFIRST for more info.-Mike
Post #1065963
 Posted Thursday, February 17, 2011 2:01 PM
 Grasshopper Group: General Forum Members Last Login: Friday, April 08, 2011 6:54 AM Points: 11, Visits: 60
 dosskavi (2/17/2011)SELECT CONVERT(CHAR(10),DATEADD(wk,2,(CURRENT_TIMESTAMP -Day(CURRENT_TIMESTAMP)+1)+(6-DATEPART(dw,(CURRENT_TIMESTAMP -Day(CURRENT_TIMESTAMP)+1)))),121)For January 2011, that formula returns the 14th, when the third Friday is on the 21st.`DECLARE @LoopDate AS DATETIMESET @LoopDate = CAST('2011-01-01' AS DATETIME)SELECTCONVERT(CHAR(10),DATEADD(wk,2,(@LoopDate -Day(@LoopDate)+1)+(6-DATEPART(dw,(@LoopDate -Day(@LoopDate)+1)))),121)2011-01-14`
Post #1065993
 Posted Thursday, February 17, 2011 4:42 PM
 Grasshopper Group: General Forum Members Last Login: Friday, April 08, 2011 6:54 AM Points: 11, Visits: 60
 abair34 (2/17/2011)SELECT CASE WHEN current_timestamp > (your 3rd week of month calculation)THEN (altered 3rd week of a month calculation based on DATEADD(MONTH, +1, currenttimestamp) instead of current_timestampELSE (your current 3rd week of the month calculation)ENDThe 3rd Friday of the month of January 2011 is in the 4th week.
Post #1066079
 Posted Thursday, February 17, 2011 4:46 PM
 SSC Rookie Group: General Forum Members Last Login: Yesterday @ 11:51 AM Points: 30, Visits: 248
 Sorry by 3rd week of the month I meant 3rd friday of the month!! :) Guilty as charged!
Post #1066080
 Posted Thursday, February 17, 2011 6:59 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 1:51 PM Points: 32,906, Visits: 26,789
 Someone like Peter Larsson will probably come up with something simpler, but this works...` SELECT CASE WHEN GETDATE() <= CAST(DATEDIFF(dd,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))/7*7+4+14 AS DATETIME) --3rd Friday Current Month THEN CAST(DATEDIFF(dd,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))/7*7+4+14 AS DATETIME) --3rd Friday Current Month ELSE CAST(DATEDIFF(dd,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))/7*7+4+14 AS DATETIME) --3rd Friday Next Month END` --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." For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1066098
 Posted Thursday, February 17, 2011 8:51 PM
 Grasshopper Group: General Forum Members Last Login: Friday, April 08, 2011 6:54 AM Points: 11, Visits: 60
 Jeff Moden (2/17/2011)` SELECT CASE WHEN GETDATE() <= CAST(DATEDIFF(dd,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))/7*7+4+14 AS DATETIME) --3rd Friday Current Month THEN CAST(DATEDIFF(dd,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))/7*7+4+14 AS DATETIME) --3rd Friday Current Month ELSE CAST(DATEDIFF(dd,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))/7*7+4+14 AS DATETIME) --3rd Friday Next Month END`This appears to report:1) The 3rd Friday in April 2011 as the 22nd, when it is the 15th.2) The 3rd Friday in July 2011 as the 22nd, when it is the 15th.
Post #1066133
 Posted Thursday, February 17, 2011 8:59 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 1:51 PM Points: 32,906, Visits: 26,789
 chris_n_osborne (2/17/2011)Jeff Moden (2/17/2011)` SELECT CASE WHEN GETDATE() <= CAST(DATEDIFF(dd,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))/7*7+4+14 AS DATETIME) --3rd Friday Current Month THEN CAST(DATEDIFF(dd,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))/7*7+4+14 AS DATETIME) --3rd Friday Current Month ELSE CAST(DATEDIFF(dd,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))/7*7+4+14 AS DATETIME) --3rd Friday Next Month END`This appears to report:1) The 3rd Friday in April 2011 as the 22nd, when it is the 15th.2) The 3rd Friday in July 2011 as the 22nd, when it is the 15th.Dang it... you're absolutely correct, Chris. Back to the drawing board. --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." For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1066134
 Posted Thursday, February 17, 2011 9:18 PM
 Grasshopper Group: General Forum Members Last Login: Friday, April 08, 2011 6:54 AM Points: 11, Visits: 60
 This appears to work, at least for 2010 and 2011 (I verified the dates against the calendar). However, it uses a look-up rather than a calculation.`BEGIN -- Show each 3rd Friday for 2010 and 2011.DECLARE @LoopCounter AS INTEGERDECLARE @LoopDate AS DATETIMESET @LoopCounter = 1SET @LoopDate = CAST('2010-01-28' AS DATETIME) -- Whatever Date is Passed (month 1 for convenience)SET @LoopDate -- Set @LoopDate to the 1st of the month. = CAST(CAST(YEAR(@LoopDate) AS VARCHAR(4)) + '-' + CAST(MONTH(@LoopDate) AS VARCHAR(2)) + '-' + CAST('01' AS VARCHAR(2)) AS DATETIME)WHILE @LoopCounter <= 24 BEGIN SELECT @LoopDate ,CASE -- Determine the 3rd Friday based on the first day of the month. WHEN DATEPART(dw, @LoopDate) = 1 THEN 20 WHEN DATEPART(dw, @LoopDate) = 2 THEN 19 WHEN DATEPART(dw, @LoopDate) = 3 THEN 18 WHEN DATEPART(dw, @LoopDate) = 4 THEN 17 WHEN DATEPART(dw, @LoopDate) = 5 THEN 16 WHEN DATEPART(dw, @LoopDate) = 6 THEN 15 WHEN DATEPART(dw, @LoopDate) = 7 THEN 21 END AS ThirdFriday SET @LoopCounter = @LoopCounter + 1 SET @LoopDate = DATEADD(month, 1, @LoopDate) ENDENDgo`
Post #1066138

 Permissions