DATE_PART scalar function
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.
| 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 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
- Example 1: Extracting the day part from a date value.
values DATE_PART('DAY', DATE('2007-02-18')); Result: 18 - Example 2: Extracting the year part from a date duration.
values DATE_PART('YEAR', cast(20130710 as decimal(8,0))); Result: 2013 - Example 3: Extracting the hour part from a time duration.
values DATE_PART('HOUR', cast(075559 as decimal(6,0))); Result: 7