NEXT_DAY

The NEXT_DAY function returns a date or timestamp value that represents the first weekday, named by string-expression, that is later than the date expression.

Read syntax diagramSkip visual syntax diagramNEXT_DAY(expression, string-expression)
expression
An expression that returns a value of one of the following built-in data types: a date, a timestamp, a character string, or a graphic string.

If expression is a character or graphic string, its value must be a valid string representation of a date or timestamp. For the valid formats of string representations of dates and timestamps, see String representations of datetime values.

string-expression
An expression that returns a built-in character string data type or graphic string data type. The value must compare equal to the full name of a day of the week or compare equal to the abbreviation of a day of the week. For example, in the English language:
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. Leading and trailing blanks are trimmed from string-expression. The resulting value is then folded to uppercase, so the characters in the value may be in any case.

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). If either argument can be null, the result can be null; if either 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.

Note

National language considerations: The values of the days of the week (or abbreviations) in string-expression may either be the US English values listed in the table above or the values based on the language used for messages in the job. The non-abbreviated name of the day is retrieved from message CPX9034 in message file QCPFMSG in library *LIBL. The abbreviated name of the day is retrieved from message CPX9039 in message file QCPFMSG in library *LIBL.

Applications that need to run in many different language environments may want to consider using US English values since they will always be accepted in the NEXT_DAY function.

Example

  • Assuming that the default language for the job is US English, set the host variable NEXTDAY with the date of the Tuesday following April 24, 2000. 
    SET :NEXTDAY = NEXT_DAY(CURRENT_DATE, 'TUESDAY')

    The host variable NEXTDAY is set with the value of '2000–04–25–00.00.00.000000', assuming that the value of the CURRENT_DATE special register is '2000–04–24'.

  • Assuming that the default language for the job is US English, set the host variable NEXTDAY with the date of the first Monday in May, 2000. Assume the host variable DAYHV = 'MON'. 
    SET :NEXTDAY = NEXT_DAY(LAST_DAY(CURRENT_TIMESTAMP), :DAYHV)

    The host variable NEXTDAY is set with the value of '2000-05-01-12.01.01.123456', assuming that the value of the CURRENT_TIMESTAMP special register is '2000-04-24-12.01.01.123456'.

  • Assuming that the default language for the job is US English, 
    SELECT NEXT_DAY('2000-04-24', 'TUESDAY')
FROM SYSIBM.SYSDUMMY1

    Returns '2000-04-25-00.00.00.000000', which is the Tuesday following '2000-04-24'.