February 18, 2011 at 5:52 am
Dave Ballantyne (2/18/2011)
Surprised that no one has mentioned using a calendar table so far.Makes it all pretty simple.
Check my code where I mentioned the possible use of a Calendar Table. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2011 at 6:06 am
Jeff Moden (2/18/2011)
Check my code where I mentioned the possible use of a Calendar Table. 🙂
My bad , far to subtle a reference for me to spot 😀
February 18, 2011 at 1:14 pm
chris,
my calendar shows april 15 2011 as the 3rd friday of the month, as april fools day is the 1st
February 18, 2011 at 3:11 pm
I've snipped out both of the additional solutions and will be studying them both. Thank you.
February 18, 2011 at 8:03 pm
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...
February 18, 2011 at 10:03 pm
yuanyelss (2/18/2011)
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...
In this case, that was the point.
February 20, 2011 at 2:30 am
Jeff Moden (2/17/2011)
Someone like Peter Larsson will probably come up with something simpler
Someone called? 😉
DECLARE@Sample TABLE
(
theDate DATE
)
INSERT@Sample
(
theDate
)
VALUES('20110314'),
('20110315'),
('20110316'),
('20110414'),
('20110415'),
('20110416')
SELECT theDate,
DATEADD(DAY, DATEDIFF(DAY, 4, DATEADD(MONTH, DATEDIFF(MONTH, -1, DATEADD(DAY, -14, theDate)), -1)) / 7 * 7, 25) AS [3rd Friday]
FROM@Sample
The solution is to know that the 3rd Friday always occurs between 15 and 21 of every month (-14).
And the I calculate the last monday of previous month and add 25 days (3 fridays * 7 + current friday [4]) to that date.
N 56°04'39.16"
E 12°55'05.25"
February 21, 2013 at 1:28 pm
I know that this is not the perfect answer. This requires use of the kimbal date dimension. If you have that, it's super simple. Use analytic functions. 🙂
SELECT
dim_d as ThridFridayOfCurrentMonth
FROM
(
SELECT dim_d,
date_us_name_str,
RANK() OVER (PARTITION BY day_of_week_num ORDER BY dim_d) RankNo
FROM dim.s0_date_dim
WHERE day_of_week_num = 6
AND month_of_year_num = DATEPART(m,GETDATE())
AND year_num = DATEPART(YEAR,GETDATE())) base
WHERE Base.RankNo = 3
February 21, 2013 at 3:54 pm
This method seems to do the job:
First find day 21 of next month, then find the Friday on or before day 21.
This code works for all datetime values in the range of 1753-01-01 00:00:00.00 thru 9999-11-30 23:59:59.997.
select
a.DT,
Day21ofNextMonth = dateadd(mm,datediff(mm,0,a.DT)+1,20),
ThirdFridayofMonth =
dateadd(dd,(datediff(dd,'17530105', dateadd(mm,datediff(mm,0,a.DT)+1,20) )/7)*7,'17530105')
from
( -- Test data
select DT = getdate()union all
select DT = dateadd(mm,-2,getdate())union all
select DT = dateadd(mm,-1,getdate())union all
select DT = dateadd(mm,1,getdate())union all
select DT = dateadd(mm,2,getdate())union all
select DT = dateadd(mm,3,getdate())union all
select DT = dateadd(mm,4,getdate())union all
select DT = dateadd(mm,5,getdate())union all
select DT = dateadd(mm,6,getdate())union all
select DT = dateadd(mm,7,getdate())union all
select DT = dateadd(mm,8,getdate())union all
select DT = dateadd(mm,9,getdate())union all
select DT = dateadd(mm,10,getdate())union all
select DT = dateadd(mm,11,getdate())union all
select DT = '17530101'union all
select DT = '99991130 23:59:59.997' -- greatest usable datetime
) a
order by
a.DT
Results:
DT Day21ofNextMonth ThirdFridayofMonth
----------------------- ----------------------- -----------------------
1753-01-01 00:00:00.000 1753-02-21 00:00:00.000 1753-02-16 00:00:00.000
2012-12-21 17:47:28.303 2013-01-21 00:00:00.000 2013-01-18 00:00:00.000
2013-01-21 17:47:28.303 2013-02-21 00:00:00.000 2013-02-15 00:00:00.000
2013-02-21 17:47:28.303 2013-03-21 00:00:00.000 2013-03-15 00:00:00.000
2013-03-21 17:47:28.303 2013-04-21 00:00:00.000 2013-04-19 00:00:00.000
2013-04-21 17:47:28.303 2013-05-21 00:00:00.000 2013-05-17 00:00:00.000
2013-05-21 17:47:28.303 2013-06-21 00:00:00.000 2013-06-21 00:00:00.000
2013-06-21 17:47:28.303 2013-07-21 00:00:00.000 2013-07-19 00:00:00.000
2013-07-21 17:47:28.303 2013-08-21 00:00:00.000 2013-08-16 00:00:00.000
2013-08-21 17:47:28.303 2013-09-21 00:00:00.000 2013-09-20 00:00:00.000
2013-09-21 17:47:28.303 2013-10-21 00:00:00.000 2013-10-18 00:00:00.000
2013-10-21 17:47:28.303 2013-11-21 00:00:00.000 2013-11-15 00:00:00.000
2013-11-21 17:47:28.303 2013-12-21 00:00:00.000 2013-12-20 00:00:00.000
2013-12-21 17:47:28.303 2014-01-21 00:00:00.000 2014-01-17 00:00:00.000
2014-01-21 17:47:28.303 2014-02-21 00:00:00.000 2014-02-21 00:00:00.000
9999-11-30 23:59:59.997 9999-12-21 00:00:00.000 9999-12-17 00:00:00.000
February 21, 2013 at 6:01 pm
A long time ago, in a galaxy far, far away, when I was but a SQL-youngling, I experimented with Calendar tables and wrote a function that would generate a calendar. Using this function (including Michael's test data plus a bit of my own) to solve this problem is relatively simple:
;WITH MyDates (MyDate) AS (
SELECT '2013-02-14' UNION ALL SELECT '2013-02-15'
UNION ALL select DT = getdate()union all
select DT = dateadd(mm,-2,getdate())union all
select DT = dateadd(mm,-1,getdate())union all
select DT = dateadd(mm,1,getdate())union all
select DT = dateadd(mm,2,getdate())union all
select DT = dateadd(mm,3,getdate())union all
select DT = dateadd(mm,4,getdate())union all
select DT = dateadd(mm,5,getdate())union all
select DT = dateadd(mm,6,getdate())union all
select DT = dateadd(mm,7,getdate())union all
select DT = dateadd(mm,8,getdate())union all
select DT = dateadd(mm,9,getdate())union all
select DT = dateadd(mm,10,getdate())union all
select DT = dateadd(mm,11,getdate())union all
select DT = '17530101'union all
select DT = '99991130 23:59:59.997')
SELECT MyDate, [Date]
FROM (
SELECT MyDate, [Date]
,rn=ROW_NUMBER() OVER (PARTITION BY MyDate ORDER BY CASE WHEN [Date] = MyDate THEN 1 ELSE 2 END DESC)
FROM MyDates
CROSS APPLY GenerateCalendar(MyDate, 31)
WHERE WkNo = 3 AND WkDName2 = 'Fr'
) a
WHERE rn=1
ORDER BY MyDate, [Date]
I am not sure whether the Force is strong in me this morning,so you might want to check that all the rows return the correct expected results. I just checked a few and they seemed to be OK.
Here is the general purpose function for Calendars:
CREATE FUNCTION GenerateCalendar
(
@FromDate DATETIME,
@NoDays INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
SELECT [SeqNo] = t.N,
[Date] = dt.DT,
[Year] = dp.YY,
[YrNN] = dp.YY % 100,
[YYYYMM] = dp.YY * 100 + dp.MM,
[BuddhaYr] = dp.YY + 543,
[Month] = dp.MM,
[Day] = dp.DD,
[WkDNo] = DATEPART(dw,dt.DT),
[WkDName] = CONVERT(NCHAR(9),dp.DW),
[WkDName2] = CONVERT(NCHAR(2),dp.DW),
[WkDName3] = CONVERT(NCHAR(3),dp.DW),
[JulDay] = dp.DY,
[JulWk] = dp.DY/7+1,
[WkNo] = dp.DD/7+1,
[Qtr] = DATEPART(qq,dt.Dt),
[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,
[LdOfMo] = DATEPART(dd,dp.LDtOfMo),
[LDtOfMo] = dp.LDtOfMo
FROM cteTally t
CROSS APPLY ( --=== Create the date
SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)
) dt
CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"
-- (Cascading CROSS APPLY, Acourtesy of ChrisM)
SELECT YY = DATEPART(yy,dt.DT),
MM = DATEPART(mm,dt.DT),
DD = DATEPART(dd,dt.DT),
DW = DATENAME(dw,dt.DT),
Dy = DATEPART(dy,dt.DT),
LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)
) dp
As the Force grew within me, a SQL-Jedi took pity on me and took me under his wing, so that I wouldn't be turned by the Dark Side of the Force. That Jedi, by the name of Jeff Moden, First Jedi-Knight of the anti-RBAR Order (in whom the midichlorians of the Force run extremely strong), instructed me in how to use the Force for good (to improve the performance of my calendar function), thereby convincing me to abandon my original effort (influenced no doubt by the Dark Side of the Force). Thanks Jeff yet again for this!
Do, or do not. There is no try!
-- Yet another Jedi
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 22, 2013 at 1:43 pm
SELECT
CASE WHEN GETDATE() >= DATEADD(DAY, 1, [current_month_3rd_friday]) THEN
--current day of month is past 3rd Fri, so calc next month's 3rd Fri
DATEADD(DAY, DATEDIFF(DAY, '19000105', DATEADD(MONTH, 1, CONVERT(char(6), GETDATE(), 112) + '21')) / 7 * 7, '19000105')
ELSE
--current day of month is not past 3rd Fri, so use current month's 3rd Fri
[current_month_3rd_friday]
END AS [3rd_Friday_Date]
FROM (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000105', CONVERT(char(6), GETDATE(), 112) + '21') / 7 * 7, '19000105')
AS [current_month_3rd_friday]
) AS [current_month_3rd_friday]
As someone earlier noted, the key is that the 3rd Friday must be on or before the 21st of the month. To make that clear in the code, I've taken the current month and forced the day to be 21. Then the "[current_month_3rd_friday]" calculation yields the first Fri on or before that date.
If that turns out to be past the current date, then I add one month to the "standard" formula to compute the 3rd Fri for the next month.
'19000105' is simply a known Fri date compatible with smalldatetime.
Note that this method works regardless of DATEFIRST and/or language settings.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply