Find the next day of the week event in SQL

Question

I am trying to update the SQL sproc WHERE clause to check if a given date falls or until the next occurrence of the class. Classes have StartDate and occur once a week on the same day every week. Given StartDate, how can I find the next occurrence of this day of the week?

eg. If StartDate is 1/18/2012, Wednesday, and I run the report today, 1/26/2012, I need to find 2/1/2012, which will be next Wednesday after 1/26. If StartDate is 1/19, Thursday, and I run the report today, the formula should give me Thu 1/26, which is today.

Here's an idea in SQL:

SELECT *
FROM tbl_Class cs
INNER JOIN tbl_Enrollment sce ON cs.pk_ClassID = sce.fk_ClassID
WHERE ...
AND sce.StartDate < [Find date of next class after @AsOfDate using cs.StartDate]

Answer 1

SQL, . 3 , , . -, WHERE, .

:

DECLARE @Startdate DATETIME,@currentdate datetime
SET @Startdate = '1-26-2012' 
SET @Currentdate = '1-23-2012'

--This section just normalizes it so you can use 7 as the interval
--The offset depends on your current setting for DATEFIRST, U.S. English default is 7, Sunday.
-- see http://msdn.microsoft.com/en-us/library/ms187766.aspx 
DECLARE @StartDateWorkingDayOfWeek int,@CurrentDateWorkingDayOfWeek int
SELECT @StartDateWorkingDayOfWeek =(DATEPART(weekday,@Startdate)-2)
SELECT @CurrentDateWorkingDayOfWeek=(DATEPART(weekday,@Currentdate)-2)

# 1

--Iteration 1 
IF @StartDateWorkingDayOfWeek < @CurrentDateWorkingDayOfWeek
SELECT DATEADD(DAY,DATEDIFF(DAY,0,@Currentdate)/7*7 + 7,@StartDateWorkingDayOfWeek)
else
SELECT DATEADD(DAY,DATEDIFF(DAY,0,@Currentdate)/7*7 + 0,@StartDateWorkingDayOfWeek)

№ 2

--Iteration 2
SELECT DATEADD(DAY,DATEDIFF(DAY,0,@Currentdate)/7*7 + 

CASE WHEN @StartDateWorkingDayOfWeek < @CurrentDateWorkingDayOfWeek
    then 7
    ELSE 0
    end

    ,@StartDateWorkingDayOfWeek)

№ 3

--iteration 3
SELECT DATEADD(DAY,DATEDIFF(DAY,0,@Currentdate)/7*7 + 

CASE WHEN (DATEPART(weekday,@Startdate)-2) < (DATEPART(weekday,@Currentdate)-2)
    then 7
    ELSE 0
    end

    ,(DATEPART(weekday,@Startdate)-2))

: http://www.sqlmag.com/article/tsql3/datetime-calculations-part-3

Answer 2

TetonSig : http://www.sqlmag.com/article/tsql3/datetime-calculations-part-3

, (@AsOfDate), :

SELECT DATEADD(day, DATEDIFF(day,0, @AsOfDate-1) /7*7, 0);

1/1/1900 @AsOfDate . /7 * 7 , 1/1/1900 (), @AsOfDate. -1 @AsOfDate. 1, @AsOfDate , "" .

, 7 :

SELECT DATEADD(d, DATEDIFF(day,0, @AsOfDate-1) /7*7, 0)+7;

Voila! @AsOfDate. , (0) - . [DayOfWeek], , . ClassDayOfWeek 0s :

DATEADD(d, DATEDIFF(d, [ClassDayOfWeek], @AsOfDate-1)/7*7, [ClassDayOfWeek])+7

ClassDayOfWeek, , @@datefirst. :

DATEDIFF(d, 0, StartDate)%7

0 Mon, 6 Sun, [ClassDayOfWeek]. , 0-6 - 1/1/1900-1/7/1900, int.

DATEADD(d, DATEDIFF(d, DATEDIFF(d, 0, StartDate)%7, @AsOfDate-1)/7*7, DATEDIFF(d, 0, StartDate)%7)+7

:

SELECT *
FROM tbl_Class cs
INNER JOIN tbl_Enrollment sce ON cs.pk_ClassID = sce.fk_ClassID
WHERE ...
AND sce.StartDate < DATEADD(d, 
                            DATEDIFF(d, 
                                     DATEDIFF(d, 0, cs.StartDate)%7, 
                                     @AsOfDate-1)/7*7, 
                            DATEDIFF(d, 0, cs.StartDate)%7)+7

Answer 3

.
@targetDOW ​​ .

DECLARE @todayDOW INT = DATEPART(dw, GETDATE());
DECLARE @diff INT = (@targetDOW - @todayDOW);

SELECT
    CASE
       WHEN @diff = 0 THEN GETDATE()
       WHEN @diff > 0 THEN DATEADD(d,@diff,GETDATE())
       WHEN @diff < 0 THEN DATEADD(d,@diff + 7,GETDATE())   
    END;