SQL - Scalar functions - NEXT_DAY

NEXT_DAY

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.

expression

An expression that returns a value of one of the following built-in data types: a DATE or a TIMESTAMP.

string-expression

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.

locale-name

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.

Notes

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).

Examples

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.
Set the variable vNEXTDAY with the timestamp of the first Monday in May, 2007.
Assume the variable vDAYOFWEEK = 'MON'.
```
     SET NEXTDAY = NEXT_DAY(LAST_DAY(CURRENT_TIMESTAMP), DAYOFWEEK)
```
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'.