Geek asks and answers

Schedule a task on SQL Server for specific months

I plan to schedule work on my SQL Server to run three times a day (8AM, 1PM, and 7PM) from January to June of each year and perform the same task once a month for the rest of the year.

I tried to find the answer, and therefore I did not succeed. Any help would be greatly appreciated.

Thank!

+3
source share
1 answer

(This has been largely addressed in the comments. I turn to their official answer.)

The built-in scheduler is so complicated. It does not support the same schedule, depending on the time of year. However, it allows multiple schedules, and you can execute your own scheduling logic in an SQL statement.

, - SQL, 4 . 3 , 1 , (8:00, 1 PM 7 PM). 30 2014 . , , , , , , . 1 2014 , 31 2014 .

, ; , . ( , - , - , .) . -, - SQL, , , . -, , , , . ( SQL, , SQL, .) , - SQL.

SQL , , , , . SQL :

DECLARE @TestDate datetime, @ExecuteSQL bit;
SELECT @TestDate = getdate(), @ExecuteSQL = 0;

-- Uncomment each of these lines (one at a time) to test the logic with various dates.
--SET @TestDate = '2014-07-02 8:00 AM';
--SET @TestDate = '2014-07-01 8:00 AM';
--SET @TestDate = '2014-07-01 9:00 AM';
--SET @TestDate = '2014-06-01 9:00 AM';
--SET @TestDate = '2014-06-02 1:00 PM';
--SET @TestDate = '2014-06-02 6:03 PM';
--SET @TestDate = '2014-06-02 7:03 PM';

SET @ExecuteSQL = 
    CASE
        WHEN
            -- it between January and June and it in the 8 AM, 1 PM, or 7 PM hours
            (
                DATEPART(m,@TestDate) BETWEEN 1 AND 6
            AND
                DATEPART(hh,@TestDate) IN (8,13,19)
            )
        OR
            -- it not between January and June, but it the first of the month during the 8 AM hour
            (
                DATEPART(m,@TestDate) NOT BETWEEN 1 AND 6
            AND
                DATEPART(d,@TestDate) = 1
            AND
                DATEPART(hh,@TestDate) IN (8)
            )
        THEN
            1
        ELSE
            0
    END;

IF @ExecuteSQL <> 0
BEGIN
    SELECT 'Do Fun Stuff Here';
END
ELSE
BEGIN
    SELECT 'You would probably omit this ELSE, but it is maintained for demonstration purposes.';
END
+2

More articles:


All Articles