Chapter 1: System Procedures

sp_modify_time_range

Description

Changes the start day, start time, end day, and/or end time associated with a named time range.

Syntax

sp_modify_time_range name, startday, endday, starttime, endtime

Parameters

name: is the name of the time range. This must be the name of a time range stored 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, or null to keep the existing startday.

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, or null to keep the existing end day. The endday can fall either earlier or later in the week than the startday, or it can be the same day as the startday.

starttime

is time of day at which the time range begins. Specify the starttime in terms of a twenty-four hour clock, with a value between 00:00 and 23:59. Use the following form, or null to keep the existing starttime:

"HH:MM"

endtime

is the time of day at which the time range ends. Specify the endtime in terms of a twenty-four hour clock, with a value between 00:00 (midnight) and 23:59. Use the following form, or null to keep the existing endtime:

"HH:MM"

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

For time ranges that span the entire day, specify a start time of “00:00” and an end time of “23:59”.

Examples

Example 1

Changes the end day of the business_hours time range from Friday to Saturday. Retains the existing start day, start time, and end time:

sp_modify_time_range business_hours, NULL, Saturday, NULL, NULL

Example 2

Specifies a new end day and end time for the before_hours time range:

sp_modify_time_range before_hours, Monday, Saturday, NULL, "08:00"

Usage

You cannot modify the “at all times” time range.
It is possible to modify a time range so that it overlaps with one or more other time ranges.
The modification of time ranges through the system stored procedures does not affect the active time ranges for sessions currently in progress.
Changes to a resource limit that has a transaction as its scope does not affect any transactions currently in progress.

Permissions

Only a System Administrator can execute sp_modify_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