NEXT_DAY Function

The NEXT_DAY function returns the earliest date that is later than its DATE or DATETIME first argument, and that falls on the day of the week that its second argument specifies. This second argument is a quoted string of three ASCII characters that abbreviates the English name of the day of the week.

The NEXT_DAY function requires two arguments:
  • a DATE or DATETIME expression that evaluates to a date earlier than the return value.
  • a character string of at least three ASCII characters that correspond to upper case letters in the range from ASCII 65 through ASCII 90. These three letters encode the abbreviation English name for a day of the week.
Successful execution of this function returns the earliest calendar date that satisfies each of two conditions:
  • The date is later than the date specified by the first argument.
  • The date falls on the day of the week specified by the second argument.
NEXT_DAY accepts the following abbreviation strings for days of the week:
Table 1. Weekday abbreviations valid as arguments to the NEXT_DAY function
Day of Week Abbreviation Day of Week Abbreviation
Sunday 'SUN' Wednesday 'WED'
Monday 'MON' Thursday 'THU'
Tuesday 'TUE' Friday 'FRI'
Saturday 'SAT'

Any characters that follow the 3rd character of these abbreviation strings are ignored. For example, both 'MONDAY' and 'MONTAG' are valid specification for the 2nd argument, each specifying the next Monday after the date in the first argument. HCL OneDB™ issues an error, however, if the second argument is a string such as 'MODNAY'whose first three characters do not match one of the weekday abbreviations in the table above.

The following query, for example, includes a valid NEXT_DAY expression: 
SELECT ship_date, NEXT_DAY(ship_date, 'SAT') AS next_saturday,
   NEXT_DAY(ship_date, 'SAT') - ship_date AS num_days FROM orders;
The result set of this query might include the following data from the orders table: 
ship_date  next_saturday    num_days

06/01/2006 06/03/2006              2
02/12/2007 02/17/2007              5
05/31/2007 06/02/2007              2
05/23/2007 05/26/2007              3

The value returned by NEXT_DAY has the same data type as the first argument. If this argument is a DATE type, NEXT_DAY returns a DATE value. If the first argument is a DATETIME type, NEXT_DAY returns a DATETIME YEAR TO FRACTION(5) value.

Because ship_date in the preceding example is a DATE column, the returned dates are formatted as DATE values, rather than in DATETIME format.

If you use a host variable to store the argument to NEXT_DAY, but the data type of the argument is not known at prepare time, HCL OneDB assumes that the data type is DATETIME YEAR TO FRACTION(5). If at runtime, after the statement has been prepared, the user supplies a DATE value for the host variable, error -9750 is issued. To prevent this error, specify the data type of the host variable by using a cast, as in this program fragment: 
sprintf(query, “, 
   “select next_day(?::date, 'SUN') from mytab”);
EXEC SQL prepare selectq from :query;
EXEC SQL declare select_cursor cursor for selectq;
EXEC SQL open select_cursor using :hostvar_date_input;
	
EXEC SQL fetch select_cursor into :var_date_output;