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
Author
Message
Posted Tuesday, February 7, 2012 7:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:34 AM
Points: 100, Visits: 80
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]

Post #1248132
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse