DAYOFWEEK scalar function

The DAYOFWEEK function returns an integer, in the range 1–7 that represents the day of the week, where 1 is Sunday and 7 is Saturday. The DAYOFWEEK function is similar to the DAYOFWEEK_ISO function.

The schema is SYSIBM.

The argument must be 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, 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 timestamp with a time zone, 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.

Examples for DAYOFWEEK

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

Example 1

The following statement uses sample table DSN8C10.EMP, set the integer host variable DAY_OF_WEEK to the day of the week that Christine Haas (EMPNO = '000010') was hired (HIREDATE). The result is that DAY_OF_WEEK is set to 6, which represents Friday.

   SELECT DAYOFWEEK(HIREDATE)
     INTO :DAY_OF_WEEK
     FROM DSN8C10.EMP
     WHERE EMPNO = '000010';

Example 2

The following query returns four values: 1, 2, 1, and 2.

   SELECT DAYOFWEEK(CAST('10/11/1998' AS DATE)),
          DAYOFWEEK(TIMESTAMP('10/12/1998', '01.02')),
          DAYOFWEEK(CAST(CAST('10/11/1998' AS DATE) AS CHAR(20))),
          DAYOFWEEK(CAST(TIMESTAMP('10/12/1998', '01.02') AS CHAR(26)))
     FROM SYSIBM.SYSDUMMY1;

Example 3

The following invocations of the DAYOFWEEK function all return the value 5, which represents Thursday. (1 represents Sunday for DAYOFWEEK results.) When the input argument contains a time zone, the result is determined from the UTC representation of the input value. The string representations of the example timestamp with time zone values in the SELECT statement all have the same UTC representation: 2003-01-02-20.00.00.

SELECT DAYOFWEEK('2003-01-02-20.00.00'), 
			DAYOFWEEK('2003-01-02-12.00.00-08:00'), 
			DAYOFWEEK('2003-01-03-05.00.00+09:00') 
		FROM SYSIBM.SYSDUMMY1;