February 17, 2011 at 12:33 pm
I'm trying to find every 3rd friday of the month
The Following code retruns for the current month
SELECT
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 date
Thanks
February 17, 2011 at 1:13 pm
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.
--Plato
February 17, 2011 at 1:17 pm
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_timestamp
ELSE (your current 3rd FRIDAY of the month calculation)
END
Be 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
February 17, 2011 at 2:01 pm
dosskavi (2/17/2011)
SELECTCONVERT(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 DATETIME
SET @LoopDate = CAST('2011-01-01' AS DATETIME)
SELECT
CONVERT(CHAR(10),
DATEADD(wk,2,(@LoopDate -
Day(@LoopDate)+1)+(6-DATEPART(dw,(@LoopDate -
Day(@LoopDate)+1)))),
121)
2011-01-14
February 17, 2011 at 4:42 pm
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_timestamp
ELSE (your current 3rd week of the month calculation)
END
The 3rd Friday of the month of January 2011 is in the 4th week.
February 17, 2011 at 4:46 pm
Sorry by 3rd week of the month I meant 3rd friday of the month!! 🙂 Guilty as charged!
February 17, 2011 at 6:59 pm
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
Change is inevitable... Change for the better is not.
February 17, 2011 at 8:51 pm
Jeff Moden (2/17/2011)
SELECT CASEWHEN 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.
February 17, 2011 at 8:59 pm
chris_n_osborne (2/17/2011)
Jeff Moden (2/17/2011)
SELECT CASEWHEN 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
Change is inevitable... Change for the better is not.
February 17, 2011 at 9:18 pm
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 INTEGER
DECLARE @LoopDate AS DATETIME
SET @LoopCounter = 1
SET @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)
END
END
go
February 17, 2011 at 9:31 pm
Sometimes I just overlook the obvious. Thanks for the hint, Chris. I'll see if I can do it with a formula.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2011 at 9:42 pm
try thisDECLARE @Date DATETIME
SELECT @Date = '2011-01-01'
SELECT Date FROM (
SELECT Number
, DATEADD(DAY, number, @Date) AS DATE
, ROW_NUMBER() OVER (PARTITION BY MONTH(DATEADD(DAY, number, @Date)), YEAR(DATEADD(DAY, number, @Date))
ORDER BY DATEADD(DAY, number, @Date)) AS ROW
FROM master..spt_values
WHERE TYPE = 'p'
AND DATENAME(DW, DATEADD(DAY, number, @Date)) = 'Friday')A
WHERE ROW = 3
ORDER BY DATE
February 17, 2011 at 11:03 pm
Fantastic, Kumar!
February 17, 2011 at 11:22 pm
No doubt about it... both of those pieces of code find the 3rd Friday of each month. But that's only the first part of the problem. If we look back at the original post...
dosskavi (2/17/2011)
I'm trying to find every 3rd friday of the monthThe Following code retruns for the current month
SELECT
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 date
Thanks
The OP already solved the problem of producing the 3rd Friday for the current date but couldn't solve the problem of...
if the current day is passed [past] the 3rd friday of the month , I need to show them the next month's 3rd Friday date
This does. The CASE statement formula is where it all happens. Again, someone can likely simplify the formula but it's fast the way it is and requires no lookup table.
DECLARE @StartYear DATETIME,
@EndYear DATETIME
;
SELECT @StartYear = '2000',
@EndYear = '2099'
;
WITH
cteBuildDates AS
( --=== This just builds a hundred years of test dates. It is not a part of the solution
-- unless the OP decides to use it to build a Calendar table for the problem.
SELECT TOP (DATEDIFF(dd,@StartYear,DATEADD(yy,1,@EndYear)))
CurrentDate = DATEADD(dd, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@StartYear)
FROM sys.All_Columns ac1
CROSS JOIN sys.All_Columns ac2
) --=== Note that -2 is a Saturday. The CASE statement solves the actual problem with no
-- character based conversions, does not rely on @@DATEFIRST (dw parts of DATEPART),
-- and requires no lookup table with additional logic to find the NEXT 3rd Friday.
-- Hat's off to Chris for the hint about the "offset" for the first of the month.
SELECT CurrentDate,
DATENAME(dw,CurrentDate) AS Dow,
CASE
WHEN CurrentDate <= DATEADD(mm,DATEDIFF(mm,0,CurrentDate),0) --First of CurrentMonth
+ (20-(DATEDIFF(dd,-2,DATEADD(mm,DATEDIFF(mm,0,CurrentDate),0))%7)) --Offset
THEN DATEADD(mm,DATEDIFF(mm,0,CurrentDate),0) --First of Current Month
+ (20-(DATEDIFF(dd,-2,DATEADD(mm,DATEDIFF(mm,0,CurrentDate),0))%7)) --Offset
ELSE DATEADD(mm,DATEDIFF(mm,0,CurrentDate)+1,0) --First of Next Month
+ (20-(DATEDIFF(dd,-2,DATEADD(mm,DATEDIFF(mm,0,CurrentDate)+1,0))%7)) --Offset
END AS Next3rdFriday
FROM cteBuildDates
;
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2011 at 1:04 am
Surprised that no one has mentioned using a calendar table so far.
Makes it all pretty simple.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply