## Get next 1st thursday

 Author Message Darren Comeau SSC-Enthusiastic Group: General Forum Members Points: 101 Visits: 90 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 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]` Iwas Bornready SSCrazy Eights Group: General Forum Members Points: 9774 Visits: 885 Thanks for the script and the updated info in comments.