Relative Date Calculation Algorithm in SQL

Question

I have a requirement to calculate the next monthly run time of a job which can be specified using two parameters which can take values from

Parameter 1:
1 for Sunday, 2 for Monday, 3 for Tuesday, 4 for Wednesday, 5 for Thursday, 6 for Friday, 7 for Saturday, 8 for Day, 9 for Weekday, 10 for Weekend day

Parameter 2:
1 for First, 2 for Second, 4 for Third, 8 for Fourth, 16 for Last

Based on these two parameters you can specify a run time like First weekday of the month or Last sunday of the month.

How do I arrive at this date using a stored procedure which will know current date, para1 and para2

@Ben Creating tags like that is rude and inappropriate. If you feel that the question doesn't show sufficient effort on the part of the asker, please downvote the question. — Yi Jiang, Feb 24 '12 at 17:05
@YiJiang, fair enough. Downvoted for asking SO to do his work for him. — Ben, Feb 24 '12 at 17:24
@Ben I tried to solve but couldnt figure out how to. I spent hours on it before posting here. I figured it was an interesting question and not much material is available online for this question. Hope I didn't offend anyone. — abhishekSikka, Feb 27 '12 at 14:11

Isaac · Answer 1 · 2012-02-24T11:58:34.700

create function schema.get_next_run_date(@day int, @week int)
returns datetime
as
begin
declare @rtResult datetime
declare @frstDayOfMonth datetime
set @frstDayOfMonth = SELECT TRUNC(current_date, 'MM') FROM dual
if @day <= 7
    set @rtResult = SELECT NEXT_DAY(@frstDayOfMonth + ((parm2-1)*7) day, param1) "NEXT DAY" FROM DUAL
else if @day = 8
    set @rtResult = SELECT NEXT_DAY(@frstDayOfMonth + pamr2) "NEXT DAY" FROM DUAL
else if @day = 9
    declare intDay int
    set @intDay = datepart(weekday, @frstDayOfMonth)
    if (@intDay between 2 and 6)
        set @rtResult = @frstDayOfMonth
    else if (@intDay = 1)
        set @rtResult = @frstDayOfMonth + 1 day
    else
        set @rtResult = @frstDayOfMonth + 2 day
else if @day = 10
    declare intDay int
    set @intDay = datepart(weekday, @frstDayOfMonth)
    if (@intDay between 2 and 6)
        set @rtResult = @frstDayOfMonth + 7 - @intDay
    else
        set @rtResult = @frstDayOfMonth
else
    set @rtResult = null
return @rtResult
end
go

not tested. but i hope this helps. And you may want to return null if the date is already overdue.

This is mixing PL/SQL and T-SQL and contains several errors not related to mixing the syntax, too. — TPete, Feb 24 '12 at 12:15
yeah.. this is my first time writing a pl/sql function.. but the idea is there...think of it as pseudo code — Isaac, Feb 25 '12 at 00:09

score 1 · Answer 2 · answered Feb 24 '12 at 12:29

This might get you started. Taken From: How-to-get-the-Nth-weekday-of-a-month

CREATE FUNCTION dbo.fnGetNthWeekdayOfMonth
(
 @theDate DATETIME,
 @theWeekday TINYINT,
 @theNth SMALLINT
)
RETURNS DATETIME
BEGIN
  RETURN  (
            SELECT  theDate
            FROM    (
                        SELECT  DATEADD(DAY, 7 * @theNth - 7 * SIGN(SIGN(@theNth) + 1) +(@theWeekday + 6 - DATEDIFF(DAY, '17530101', DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')) % 7) % 7, DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')) AS theDate
                        WHERE   @theWeekday BETWEEN 1 AND 7
                                AND @theNth IN (-5, -4, -3, -2, -1, 1, 2, 3, 4, 5)
                    ) AS d
            WHERE   DATEDIFF(MONTH, theDate, @theDate) = 0
        )
END

It's not exactly what you are looking for, but should cover the main part.

Relative Date Calculation Algorithm in SQL

2 Answers2