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.

Read syntax diagramSkip visual syntax diagramNEXT_DAY(expression, string-expression locale-name )

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

  • 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 = 'MON'. 
    
     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'.