DAY scalar function

The DAY function returns the day part of a value.

Read syntax diagramSkip visual syntax diagramDAY( expression)

The schema is SYSIBM.

The argument must be an expression that returns one of the following built-in data types: a date, a timestamp, a character string, a graphic string, or any numeric data type.

  • 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 that is not greater than 255 bytes. For the valid formats of string representations of dates and timestamps, see String representations of datetime values.
  • If expression is a number, it must be a date duration or a timestamp duration. For the valid formats of datetime durations, see Datetime operands.

If expression is a timestamp with a time zone value, or a valid string representation of a timestamp with a time zone, the result is determined from the UTC representation of the datetime value.

The result of the function is a large integer.

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

The other rules for the function depend on the data type of the argument:

  • If the argument is a date, timestamp, or string representation of either, the result is the day part of the value, which is an integer in the range 1–31.
  • If the argument is a date duration or timestamp duration, the result is the day part of the value, which is an integer between -99 and 99. A nonzero result has the same sign as the argument.
  • Start of changeIf the argument contains a time zone, the result is the day part of the value expressed in UTC.End of change

Examples for DAY

Example 1
Set the INTEGER host variable DAYVAR to the day of the month on which employee 140 in the sample table DSN8C10.EMP was hired. 
   EXEC SQL SELECT DAY(HIREDATE)
     INTO :DAYVAR
     FROM DSN8C10.EMP
     WHERE EMPNO = '000140';
Example 2
Assume that DATE1 and DATE2 are DATE columns in the same table. Assume also that for a given row in this table, DATE1 and DATE2 represent the dates 15 January 2000 and 31 December 1999, respectively. Then, for the given row, the following function invocation returns 15.
   DAY(DATE1 - DATE2)
Example 3
The following invocations of the DAY function all return the same result, which is 2. When the input argument contains a time zone, the result is determined from the UTC representation of the input value. The string representations of timestamps with a time zone in the example SELECT statement all have the same UTC representation: 2003-01-02-20.00.00. The day portion of the UTC representation is 2.
 SELECT DAY('2003-01-02-20.00.00'), 
			DAY('2003-01-02-12.00.00-08:00'), 
			DAY('2003-01-03-05.00.00+09:00') 
	FROM SYSIBM.SYSDUMMY1;