Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How are SQL Server sysschedules intervals calculated?

Tags:

sql

sql-server

This table: sysschedules

If the freq_type is 8 (weekly) then freq_interval is 1,2,4,8,16,32,64 for Su,M,T,W,Th,F,S.

But when freq_type is 32 (monthly relative) then freq_interval is 1,2,3,4,5,6,7,8,9,10 for Su,M,T,W,Th,F,S, weekday, weekend.

Why is this? Is there some sort of calculation occurring to reduce query complexity?

If so, how? (sry if I sound clueless, building a recurring events system is melting my brain)

like image 957
Chaddeus Avatar asked Feb 14 '12 08:02

Chaddeus


People also ask

How do you define an interval in SQL?

Use the interval data type to declare host variables for database values of type INTERVAL. You can use the Informix ESQL/C library functions dtcvasc(), dtcvfmtasc(), dttoasc(), and dttofmtasc() to explicitly convert between DATETIME column values and character strings.

How do I count days between two dates in SQL?

The DATEDIFF() function returns the difference between two dates.

How do I schedule a SQL query to run monthly?

In the 'Steps' window enter a step name and select the database you want the query to run against. Paste in the T-SQL command you want to run into the Command window and click 'OK' . Click on the 'Schedule' menu on the left of the New Job window and enter the schedule information (e.g. daily and a time).


1 Answers

If freq_type is 8, then the job can occur on one or more days - so there needs to be a way to represent multiple days in the freq_interval column. This is done by assigning each day a power of two value, and so multiple values can be added together unambiguously. E.g. for something that occurs on Monday, Wednesday and Friday, the value would be 2+8+32 = 42. (Your question has the wrong values assigned to the days)

If freq_type is 32, then only a single option can be selected - one day of the week, or day, weekday or weekend. Since multiple values cannot be combined, a simpler representation can be used.

like image 159
Damien_The_Unbeliever Avatar answered Sep 17 '22 17:09

Damien_The_Unbeliever