Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Get next 1st thursday Expand / Collapse
Posted Tuesday, February 7, 2012 7:58 AM


Group: General Forum Members
Last Login: Thursday, September 24, 2015 2:33 AM
Points: 101, Visits: 87
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.
@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

-- 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

@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)
-- uncomment for Debug
--SELECT 'First Thursday This month'
SET @monthForward = 0
-- uncomment for Debug
--SELECT 'First Thursday Next month'
SET @monthForward = 1

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]

Post #1248132
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse