DB2 Scalar functions - DATE_PART - www.cadcobol.com.br

DATE_PART

The DATE_PART function returns a portion of a datetime based on its arguments.
It extracts the subfield that is specified from the date, time, timestamp, and duration values.

The schema is SYSIBM.

format-string: An expression that represents which unit of datetime is to be extracted from the date-expression.
The expression that returns a built-in character string data type with an actual length that is not greater than 255 bytes.
The format element in format-string specifies how the datetime-expression should be truncated.
Leading and trailing blanks are removed from the string, and the resulting substring must be a valid format element for the type of datetime-expression (SQLSTATE 22007). Table 1 lists the allowable values for format-string.
datetime-expression: An expression that specifies the datetime value from which the unit specified by format-string is extracted.
The expression must return a value that is a DATE, TIME, TIMESTAMP, date duration, time duration, or timestamp duration.

Table 1. Allowable values for format-string

format-string	Description
EPOCH	The number of seconds since 1970-01-01 00:00:00.00. The value can be positive or negative. Valid for DATE, TIME, and TIMESTAMP.
MILLENNIUM/MILLENNIUMS	The millennium; for example, 2 indicates a date between 01 Jan 2000 and 31 Dec 2999. Valid for DATE, TIMESTAMP, date duration, and timestamp duration.
CENTURY/CENTURIES	The number of full 100-year periods represented by the year; for example, 20 indicates a date between 01 Jan 2000 and 31 Dec 2099. Valid for DATE, TIMESTAMP, date duration, and timestamp duration.
DECADE/DECADES	The number of full 10-year periods represented by the year; for example, 201 indicates a date between 01 Jan 2010 and 31 Dec 2019. Valid for DATE, TIMESTAMP, date duration, and timestamp duration.
YEAR/YEARS	The year; for example, 2015. Valid for DATE, TIMESTAMP, date duration , timestamp duration.
QUARTER	The quarter of the year (1 - 4) that the specified day is in. Valid for DATE, TIMESTAMP, date duration, and timestamp duration.
MONTH/MONTHS	The number of the month within the year (1 - 12). Valid for DATE, TIMESTAMP, date duration, and timestamp duration.
WEEK	The number of the week of the year (1 - 53) that the specified day is in. The value uses the ISO-8601 definition of a week, which begins on Monday; as a result, some years might have 53 weeks, and sometimes the first few days of January can be included as part of the 52nd or 53rd week of the previous year. Valid for DATE and TIMESTAMP.
DAY/DAYS	The day of the month (1 - 31). Valid for DATE, TIMESTAMP, date duration, and timestamp duration.
DOW	The day of the week, from 1 (Sunday) to 7 (Saturday). Valid for DATE and TIMESTAMP.
DOY	The day of the year (1 - 366). Valid for DATE and TIMESTAMP.
HOUR/HOURS	The hour of the day (0 – 23). Valid for TIME, TIMESTAMP, time duration, and timestamp duration.
MINUTE/MINUTES	The minute of the hour (0 – 59). Valid for TIME, TIMESTAMP, time duration, and timestamp duration.
SECOND/SECONDS	The second of the minute, not including fractional parts (0 – 59). Valid for TIME, TIMESTAMP, time duration, and timestamp duration.
MILLISECOND/MILLISECONDS	The second of the minute, including fractional parts to one thousandth of a second, multiplied by 1000 (0 – 59999). Valid for TIMESTAMP and timestamp duration.
MICROSECOND/MICROSECONDS	The second of the minute, including fractional parts to one millionth of a second, multiplied by 1000000 (0 – 59999999). Valid for TIMESTAMP and timestamp duration.

The format-string values are case insensitive.

The data type of the result of this function is BIGINT. If the argument can be null, the result can be null; if the argument is null, the result is the null value.

Examples

Extracting the day part from a date value.

values DATE_PART('DAY', DATE('2007-02-18'));
Result: 18

Extracting the year part from a date duration.

values DATE_PART('YEAR', cast(20130710 as decimal(8,0)));
Result: 2013

Extracting the hour part from a time duration.

values DATE_PART('HOUR', cast(075559 as decimal(6,0)));
Result: 7