SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Harsha Majety / Get next 1st thursday / Latest Posts

RE: Get next 1st thursday

Darren Comeau — Tue, 07 Feb 2012 07:58:08 GMT

Hi All,Sorry I'm late to the party, but just wanted to post something for anyone to comment on as another method of solving the problem using date diff and % and a seed date to avoid changing first day of week issues. Personally I'd drop the timestamp part which makes it determanistic when packaged as a function so you could use it in persisted computed columns etc.[code="sql"]/* * Find the next "First Thursday of the month" after the input date * * Mathematical solution avoiding external dependencies. * * Darren Comeau - 7th Feb 2012. * */DECLARE @inputDate DATETIME ,@workDate DATETIME ,@outputDate DATETIMESET @inputDate = COALESCE(@inputDate,CURRENT_TIMESTAMP) -- If no input date passed take today.SET @inputDate = DATEADD(day,DATEDIFF(day,'19000101',@inputDate),'19000101') -- remove any time componentDECLARE-- any Thursday date in time. Could design the function using @@DATEFIRST but perhaps this is more portable. @seedDate DATETIME--SET @seedDate = '19990520' -- Thursday 20th May, 1999.SET @seedDate = '20120320' -- Tuesday 20th March, 2012. -- would give first tuesday of the monthDECLARE @dowToday TINYINT ,@dowWorkDate TINYINT ,@monthForward TINYINT -- The value to add to this month-- What day is today, Thursday = 1, Wednesday = 7SET @dowToday = (((DATEDIFF(day,@seedDate,@inputDate))%7)+7)%7 + 1-- This will handle the seed date being in the future or past, the %7+7 handles negative datediff results from future dates-- uncomment for Debug --SELECT @dowToday AS [dow],DATEPART(day,@inputDate) AS [dom]-- Has there already been a thursday in this month?IF @dowToday > DATEPART(day,@inputDate)BEGIN -- uncomment for Debug --SELECT 'First Thursday This month' SET @monthForward = 0ENDELSEBEGIN -- uncomment for Debug --SELECT 'First Thursday Next month' SET @monthForward = 1ENDSET @workDate = DATEADD(month,@monthForward,@inputDate)SET @dowWorkDate = (((DATEDIFF(day,@seedDate,@workDate))%7)+7)%7 + 1SELECT @outputDate = DATEADD(day ,(DATEPART(day,@workDate) +7 -@dowWorkDate)%7 -- add n days to the first day of the month to return the thursday. ,DATEADD(day,((DATEPART(day,@workDate)-1)*-1),@workDate) -- Get the first day of the month)-- format the date dd/mm/yyyySELECT CONVERT(CHAR(10),@outputDate,103) AS [NextFirstThursday][/code]

RE: Get next 1st thursday

nigel. — Wed, 25 Jan 2012 07:03:08 GMT

Jonathan,I think you may have cracked it there, I suspected mine was too complicated.With one slight modification to your where clause to account for different DATEFIRST settings:[code="sql"] WHERE (@@DATEFIRST + DATEPART(WEEKDAY, NextDay)) %7 = @DesiredDay AND DAY(NextDay) <= 7[/code]Nice one! :cool:

RE: Get next 1st thursday

fahey.jonathan — Wed, 25 Jan 2012 06:22:54 GMT

Because we know that the first Thursday of a month must be within 40 days of the selected date and must have a day number between 1 and 7, I can modify my original query with those parameters:[code="sql"]DECLARE @SelectedDate DATE = '2012-02-02' --GETDATE()DECLARE @DesiredDay TINYINT = 5SELECT TOP 1 NextDay, DATENAME(WEEKDAY, NextDay) AS NameOfDayFROM ( SELECT DATEADD(DAY, N, @SelectedDate) AS NextDay FROM Tally WHERE N <= 40 ) xWHERE DATEPART(WEEKDAY, NextDay) = @DesiredDay AND DAY(NextDay) <= 7ORDER BY NextDay[/code]I think it is that easy. Let me know if I missed something.

RE: Get next 1st thursday

nigel. — Wed, 25 Jan 2012 01:52:44 GMT

[quote][b]fahey.jonathan (1/24/2012)[/b][hr][quote]Jonathan, Nearly but not quite, that just gets next thursday. Not the next first thursday of the month.[/quote]I misread the original post. I thought the goal was to find the next Thursday, not the first Thursday of a month past today. Sorry.[/quote]Easily done :-), to be honest it wasn't particularly well defined in the article

RE: Get next 1st thursday

fahey.jonathan — Tue, 24 Jan 2012 13:19:31 GMT

[quote]Jonathan, Nearly but not quite, that just gets next thursday. Not the next first thursday of the month.[/quote]I misread the original post. I thought the goal was to find the next Thursday, not the first Thursday of a month past today. Sorry.

RE: Get next 1st thursday

nigel. — Tue, 24 Jan 2012 08:59:54 GMT

[quote][b]fahey.jonathan (1/20/2012)[/b][hr]Here is an alternate version using a Tally table.[/quote]Jonathan, Nearly but not quite, that just gets next thursday. Not the next first thursday of the month.Here's another version loosely based on your Tally table idea.I'll try to explain what it does: 1. Gets all days for this and the next month. 2. Reduces that set to just Thursdays 3. Ranks partitioned by month, to number the Thursdays 1 - n 4. Gets the top Thursday with a rank of 1 that is greater than the target date[code="sql"]DECLARE @now DATETIME = '20120201'SELECT TOP 1 theDayFROM( SELECT theDay, --== --== Rank the thursdays 1-4 (or possibly 5) --== theRank = RANK() OVER(PARTITION BY MONTH(theDay) ORDER BY theDay) FROM ( --== --== Get all the days in this and next month --== SELECT DATEADD(dd,n-1,DATEADD(mm, DATEDIFF(mm,'19000101',@now ) ,'19000101')) theDay FROM Tally WHERE N <= DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm,'19000101',@now) ,'19000101'), DATEADD(mm, 2+DATEDIFF(mm,'19000101',@now) ,'19000101')) ) alldays WHERE --== --== Get just the Thursdays (note the adjustment for a DATEFIRST setting other than 7) --== (@@DATEFIRST + DATEPART(dw,theDay)) % 7 = 5 ) ThursdaysWHERE theRank = 1 -- The first thursday AND theDay > @now -- After the target dateORDER BY theDay[/code]

RE: Get next 1st thursday

fahey.jonathan — Fri, 20 Jan 2012 10:15:06 GMT

Here is an alternate version using a Tally table.[code="sql"]DECLARE @SelectedDate DATE = '2012-02-01' --GETDATE()DECLARE @DesiredDay TINYINT = 5SELECT TOP 1 NextDay, DATENAME(WEEKDAY, NextDay) AS NameOfDayFROM ( SELECT DATEADD(DAY, N, @SelectedDate) AS NextDay FROM Tally WHERE N <= 14 ) xWHERE DATEPART(WEEKDAY, NextDay) = @DesiredDayORDER BY NextDay[/code]It should be easy to turn into a function.

RE: Get next 1st thursday

nigel. — Fri, 20 Jan 2012 02:42:11 GMT

Matt,There is however a difference I'd not spotted initially :blush: between the solutions for the 1 Feb 2012The original solutions result is: 2 Feb 2012Yours is: 1 Mar 2012 :w00t:

RE: Get next 1st thursday

nigel. — Fri, 20 Jan 2012 02:24:35 GMT

Nice one matt, I thought the original looked well overcomplicated when I saw it.thanks

RE: Get next 1st thursday

matt_chen — Wed, 28 Dec 2011 20:18:22 GMT

SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)),3)

Get next 1st thursday

harsha.majety — Tue, 20 Dec 2011 14:13:41 GMT

Comments posted to this topic are about the item [B]Get next 1st thursday[/B]