sp_add_time_range

Description

Adds a named time range to an Adaptive Server.

Syntax

sp_add_time_range name, startday, endday, starttime, endtime

Parameters

name

is the name of the time range. Time range names must be 255 characters or fewer. The name cannot already exist in the systimeranges system table of the master database.

startday

is the day of the week on which the time range begins. This must be the full weekday name for the default server language, as stored in the syslanguages system table of the master database.

endday

is the day of the week on which the time range ends. This must be the full weekday name for the default server language, as stored in the syslanguages system table of the master database. The endday can fall either earlier or later in the week than the startday or can be the same day as the startday.

starttime

is the time of day when the time range begins. Specify the starttime in terms of a 24-hour clock, with a value between “00:00” (midnight) and “23:59” (11:59 p.m.). Use the following form:

"HH:MM"
endtime

is the time of day when the time range ends. Specify the endtime in terms of a 24-hour clock, with a value between “00:00” (midnight) and “23:59” (11:59 p.m.). Use the following form:

"HH:MM"

NoteTo create a time range that spans the entire day, specify both a start time and an end time of “00:00”.

The endtime must occur later in the day than the starttime, unless endtime is “00:00”.

Examples

Example 1

Creates the business_hours time range, which is active Monday through Friday, from 9:00 a.m. to 5:00 p.m.:

sp_add_time_range business_hours, monday, Friday, "09:00", "17:00"

Example 2

Creates two time ranges, before_hours and after_hours, that, together, span all non-business hours Monday through Friday. The before_hours time range covers the period from 12:00 midnight to 9:00 a.m., Monday through Friday. The after_hours time range covers the period from 6:00 p.m. through 12:00 midnight, Monday through Friday:

sp_add_time_range before_hours, Monday, Friday, "00:00", "09:00"
sp_add_time_range after_hours, Monday, Friday, "18:00", "00:00"

Example 3

Creates the weekends time range, which is 12:00 midnight Saturday to 12:00 midnight Sunday:

sp_add_time_range weekends, Saturday, Sunday, "00:00", "00:00"

Example 4

Creates the Fri_thru_Mon time range, which is 9:00 a.m. to 5:00 p.m., Friday, Saturday, Sunday, and Monday:

sp_add_time_range Fri_thru_Mon, Friday, Monday, "09:00", "17:00"

Example 5

Creates the Wednesday_night time range, which is Wednesday from 5:00 p.m. to 12:00 midnight:

sp_add_time_range Wednesday_night, Wednesday, Wednesday, "17:00", "00:00"

Usage

Permissions

Only a System Administrator can execute sp_add_time_range.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

System procedures sp_add_resource_limit, sp_drop_time_range, sp_modify_time_range