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

  • 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

  • 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

  • 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

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

  • 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

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

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

  • 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

  • Declare @NextDOW Int,@Date SmalldateTime

    Set @NextDOW=2

    Set @Date='20111205'

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

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

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