December 20, 2011 at 2:13 pm
Comments posted to this topic are about the item Get next 1st thursday
December 28, 2011 at 8:18 pm
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)),3)
January 20, 2012 at 2:24 am
Nice one matt, I thought the original looked well overcomplicated when I saw it.
thanks
January 20, 2012 at 2:42 am
Matt,
There is however a difference I'd not spotted initially :blush: between the solutions for the 1 Feb 2012
The original solutions result is: 2 Feb 2012
Yours is: 1 Mar 2012 :w00t:
January 20, 2012 at 10:15 am
Here is an alternate version using a Tally table.
DECLARE@SelectedDateDATE = '2012-02-01' --GETDATE()
DECLARE@DesiredDayTINYINT = 5
SELECTTOP 1 NextDay, DATENAME(WEEKDAY, NextDay) AS NameOfDay
FROM(
SELECTDATEADD(DAY, N, @SelectedDate) AS NextDay
FROMTally
WHEREN <= 14
) x
WHEREDATEPART(WEEKDAY, NextDay) = @DesiredDay
ORDER BY NextDay
It should be easy to turn into a function.
January 24, 2012 at 8:59 am
fahey.jonathan (1/20/2012)
Here is an alternate version using a Tally table.
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
DECLARE @now DATETIME = '20120201'
SELECT TOP 1
theDay
FROM
(
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
) Thursdays
WHERE
theRank = 1-- The first thursday
AND
theDay > @now-- After the target date
ORDER BY theDay
January 24, 2012 at 1:19 pm
Jonathan,
Nearly but not quite, that just gets next thursday. Not the next first thursday of the month.
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.
January 25, 2012 at 1:52 am
fahey.jonathan (1/24/2012)
Jonathan,
Nearly but not quite, that just gets next thursday. Not the next first thursday of the month.
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.
Easily done :-), to be honest it wasn't particularly well defined in the article
January 25, 2012 at 6:22 am
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:
DECLARE@SelectedDateDATE = '2012-02-02' --GETDATE()
DECLARE@DesiredDayTINYINT = 5
SELECTTOP 1 NextDay, DATENAME(WEEKDAY, NextDay) AS NameOfDay
FROM(
SELECTDATEADD(DAY, N, @SelectedDate) AS NextDay
FROMTally
WHEREN <= 40
) x
WHEREDATEPART(WEEKDAY, NextDay) = @DesiredDay
ANDDAY(NextDay) <= 7
ORDER BY NextDay
I think it is that easy. Let me know if I missed something.
January 25, 2012 at 7:03 am
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:
WHERE
(@@DATEFIRST + DATEPART(WEEKDAY, NextDay)) %7 = @DesiredDay
AND
DAY(NextDay) <= 7
Nice one! 😎
February 7, 2012 at 7:58 am
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.
/*
* 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 DATETIME
SET @inputDate = COALESCE(@inputDate,CURRENT_TIMESTAMP) -- If no input date passed take today.
SET @inputDate = DATEADD(day,DATEDIFF(day,'19000101',@inputDate),'19000101') -- remove any time component
DECLARE
-- 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 month
DECLARE
@dowToday TINYINT
,@dowWorkDate TINYINT
,@monthForward TINYINT -- The value to add to this month
-- What day is today, Thursday = 1, Wednesday = 7
SET @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 = 0
END
ELSE
BEGIN
-- uncomment for Debug
--SELECT 'First Thursday Next month'
SET @monthForward = 1
END
SET @workDate = DATEADD(month,@monthForward,@inputDate)
SET @dowWorkDate = (((DATEDIFF(day,@seedDate,@workDate))%7)+7)%7 + 1
SELECT @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/yyyy
SELECT CONVERT(CHAR(10),@outputDate,103) AS [NextFirstThursday]
May 11, 2016 at 7:24 am
Thanks for the script and the updated info in comments.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply