NEXT_DAY scalar function
The NEXT_DAY scalar function returns a datetime value that represents the first weekday, named by string-expression, that is later than the date in expression.
The schema is SYSIBM.
- An expression that returns a value of one of the following built-in data types: a DATE or a TIMESTAMP.
- An expression that returns a built-in character data type. The
value must be a valid day of the week for the locale-name.
The value can be specified either as the full name of the day or the
associated abbreviation. For example, if the locale is 'en_US'
then the following values are valid:
Table 1. Valid day names and abbreviations for the 'en_US' locale Day of week Abbreviation MONDAY MON TUESDAY TUE WEDNESDAY WED THURSDAY THU FRIDAY FRI SATURDAY SAT SUNDAY SUN
The minimum length of the input value is the length of the abbreviation. The characters can be specified in lower or upper case and any characters immediately following a valid abbreviation are ignored.
- A character constant that specifies the locale used to determine
the language of the string-expression value.
The value of locale-name is not case sensitive
and must be a valid locale (SQLSTATE 42815). For information about
valid locales and their naming, see
Locale names for SQL and XQuery. If locale-name is not specified, the value of the special register CURRENT LOCALE LC_TIME is used.
The result of the function has the same data type as expression, unless expression is a string, in which case the result data type is TIMESTAMP(6) . The result can be null; if any argument is null, the result is the null value.
Any hours, minutes, seconds or fractional seconds information included in expression is not changed by the function. If expression is a string representing a date, the time information in the resulting TIMESTAMP value is all set to zero.
- Determinism: NEXT_DAY is a deterministic function. However, when locale-name is not explicitly specified, the invocation of the function depends on the value of the special register CURRENT LOCALE LC_TIME. Invocations of the function that depend on the value of a special register cannot be used wherever special registers cannot be used (SQLSTATE 42621, 428EC, or 429BX).
- Example 1: Set the variable NEXTDAY with the date of the
Tuesday following April 24, 2007.
SET NEXTDAY = NEXT_DAY(DATE '2007-04-24', 'TUESDAY')
The variable NEXTDAY is set with the value of '2007-05-01', since April 24, 2007 is itself a Tuesday.
- Example 2: Set the variable vNEXTDAY with the timestamp
of the first Monday in May, 2007. Assume the variable vDAYOFWEEK =
SET vNEXTDAY = NEXT_DAY(LAST_DAY(CURRENT_TIMESTAMP),vDAYOFWEEK)
The variable vNEXTDAY is set with the value of '2007-05-07-12.01.01.123456', assuming that the value of the CURRENT_TIMESTAMP special register is '2007-04-24-12.01.01.123456'.