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]