NEXT_DAY

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

Read syntax diagram
>>-NEXT_DAY(expression,string-expression)----------------------><

The schema is SYSIBM.

If expression is a timestamp or valid string representation of a timestamp, the timestamp value has the same hours, minutes, seconds, and partial seconds as expression. If expression is a date, or a valid string representation of a date, then the hours, minutes, seconds, and partial seconds value of the result is 0.

expression
An expression that returns 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, it must not be a CLOB or DBCLOB, and its value must be a valid string representation of a date or timestamp with an actual length of not greater than 255 bytes. Start of changeA time zone in a string representation of a timestamp is ignored.End of change For the valid formats of string representations of dates and timestamps, see String representations of datetime values.

Start of changeIf expression does not have data type TIMESTAMP WITHOUT TIME ZONE, expression is cast as follows:End of change

Start of change
  • If expression is a TIMESTAMP WITH TIME ZONE value, expression is cast to TIMESTAMP WITHOUT TIME ZONE, with the same precision as expression.
  • Otherwise, expression is cast to TIMESTAMP(6) WITHOUT TIME ZONE.
End of change
string-expression
An expression that returns a built-in character or graphic string data type that is not a LOB. For portability across the platforms, the value should compare equal to the full name of a day of the week or should compare equal to the abbreviation of a day of the week. For example:
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 blanks must not be specified in string-expression. Trailing blanks are trimmed from string-expression. The resulting value is folded to uppercase. Any characters other than blank that immediately follow a valid abbreviation are ignored.

Start of changeIf expression is a timestamp, the result is a TIMESTAMP WITHOUT TIME ZONE value with the same precision as expression. If expression is DATE, the result is a DATE value. Otherwise, the result is a TIMESTAMP(6) WITHOUT TIME ZONE value.End of change

Start of changeAny hours, minutes, seconds, or fractional seconds information that is included in expression is not changed by the function. If expression is a string that represents a date, the time information in the resulting timestamp value is all set to zero.End of change

The result can be null; if any argument is null, the result is the null value.

The result CCSID is the appropriate CCSID of the argument encoding scheme and the result subtype is the appropriate subtype of the CCSID.

Example 1: Set the host variable NEXTDAY with a timestamp for the date of the Tuesday that follows April 24, 2007. 
   SET :NEXTDAY = NEXT_DAY(TIMESTAMP '2007-04-24-00.00.00.000000', 'TUESDAY');
The host variable NEXTDAY is set with the value of '2007-05-01-00.00.00.000000', since April, 24, 200 is itself a Tuesday'.
Example 2: Set the host variable vNEXTDAY with the date of the first Monday in May, 2007. Assume the host variable vDAYOFWEEK = 'MON':
   SET :vNEXTDAY = NEXT_DAY(LAST_DAY(CURRENT_DATE),:vDAYOFWEEK);
The host variable vNEXTDAY is set with the value of '2007-05-07', assuming that the value of the CURRENT_DATE special register is '2007-04-24'.