Finding the next date after a start date that is a certain day of the week

Question

Post reply

Finding the next date after a start date that is a certain day of the week

kangarolf

Hall of Fame

Points: 3432
More actions
November 29, 2011 at 9:33 am

#100790

Hi all,
Been trying to figure out a decent way of getting the next specified day of the week from a startdate.
Easy enough if the specified day is on or after the startdate but if I wanted the next monday from a startdate that is a tuesday my generalised function doesnt work and gives me the date of the Monday just gone (yesterday).
I'm sure I am being dense and this is not difficult so can anyone put me out of my misery.
Rolf

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

larsjessenhh SSC Veteran Points: 280 More actions · Answer 1

Hello Rolf,

maybe this way:

DECLARE @startdate DATE;

DECLARE @daytofind INT;

SET @startdate = '2011-11-29';

SET @daytofind = 2;

WITH myWeek AS

(SELECT @startdate AS myDay, DATEPART(WEEKDAY, @startdate) as MyDayofWeek

UNION ALL

SELECT DATEADD(dd,1,myDay) AS myDay, DATEPART(WEEKDAY, DATEADD(dd,1,myDay)) as MyDayofWeek FROM myWeek WHERE DATEDIFF(dd,@startdate,myday) < 6)

SELECT myDay FROM myWeek WHERE MyDayofWeek = @daytofind

you can change the root element of the recursion to a DATEADD if you don't want to include the startdate in the search

Lars

larsjessenhh SSC Veteran Points: 280 More actions · Answer 2

OK, sorry, just realized that this is the subforum for 2000, so the query above wouldn't work, cte is a feature of 2005 and above...

I'm just starting to find my way around here in this community, I'll try to read more carefully next time before posting!

Lars

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

There's probably a way to do this with just a DATEADD statement, but off the top of my head I can't think of one.

Anyway, this should work: -

DECLARE @startdate DATETIME

SET @startdate = '2011-11-30'

SELECT CASE WHEN DATEDIFF(dd,0,@startdate) %7 = 5

THEN DATEADD(DAY,2,@startdate)

WHEN DATEDIFF(dd,0,@startdate) %7 = 4

THEN DATEADD(DAY,3,@startdate)

ELSE DATEADD(DAY,1,@startdate) END

--EDIT--

Although syntactically no different, the below looks a little better than the above.

DECLARE @startdate DATETIME

SET @startdate = '2011-11-18';

SELECT DATEADD(DAY,CASE WHEN DATEDIFF(dd,0,@startdate) %7 = 5

THEN 2

WHEN DATEDIFF(dd,0,@startdate) %7 = 4

THEN 3

ELSE 1 END,@startdate)

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

kangarolf Hall of Fame Points: 3432 More actions · Answer 4

Yes I've been scratching my head looking for a simple one liner dateadd statement that will get it but I my feeble brain cant work it out so I have ended up with some sort of choice/logic in there using either CASE, WHERE or IF in the T-SQL.

Thanks for the input....can any boffins out there improve on this..?

Rolf

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Another method -

DECLARE @startdate DATETIME

SET @startdate = '2011-11-18'

SELECT TOP 1 DATEADD(dd, Num, DATEDIFF(dd, 0, @startdate))

FROM (SELECT 1 AS Num UNION ALL SELECT 2 UNION ALL SELECT 3) AS a

WHERE (DATEDIFF(dd, 0, @startdate) + Num) % 7 <= 4

ORDER BY DATEADD(dd, Num, DATEDIFF(dd, 0, @startdate))

--EDIT--

And another -

DECLARE @startdate DATETIME

SET @startdate = '2011-11-18'

SELECT DATEADD(dd, a.ID, DATEDIFF(dd, 0, @startdate))

FROM (SELECT 1 AS ID, (DATEDIFF(dd, 0, @startdate) + 1) % 7 AS checker

UNION ALL SELECT 2, (DATEDIFF(dd, 0, @startdate) + 2) % 7

UNION ALL SELECT 3, (DATEDIFF(dd, 0, @startdate) + 3) % 7) a

LEFT OUTER JOIN (SELECT 1 AS ID, (DATEDIFF(dd, 0, @startdate) + 1) % 7 AS checker

UNION ALL SELECT 2, (DATEDIFF(dd, 0, @startdate) + 2) % 7

UNION ALL SELECT 3, (DATEDIFF(dd, 0, @startdate) + 3) % 7) b ON a.checker > b.checker

WHERE b.ID IS NULL

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

And another, far uglier method

DECLARE @startdate DATETIME

SET @startdate = '2011-11-18'

SELECT DATEADD(DAY,ISNULL(NULLIF(ISNULL(NULLIF(ISNULL(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(DATEDIFF(dd,0,@startdate)%7,0),1),2),3),6),1),4),3),5),2), @startdate)

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sergiy SSC Guru Points: 110209 More actions · Answer 7

DECLARE @NeededDW tinyint, @AfterDate datetime

SET @NeededDW = 5

SET @AfterDate = GETDATE()

select MIN(Date)

from Tally

WHERE Date > @AfterDate

AND DayOfWeek = @NeededDW

_____________
Code for TallyGenerator

Edward Boyle-478467 Ten Centuries Points: 1165 More actions · Answer 8

Declare @NextDOW Int,@Date SmalldateTime

Set @NextDOW=2

Set @Date='20111205'

Select dateadd(dd,-datediff(dd,6+@NextDOW,@Date)%7+7,@Date)

Finding the next date after a start date that is a certain day of the week

Cookies on SQLServerCentral