date_part function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Extracts a part of the date, timestamp, or interval.

Syntax 

date_part(fieldStr, expr)

Arguments

  • fieldStr: An STRING literal.

  • expr: A DATE, TIMESTAMP, or INTERVAL expression.

Returns

If fieldStr is 'SECOND', a DECIMAL(8, 6). In all other cases, an INTEGER.

Supported values of field when source is DATE or TIMESTAMP:

  • 'YEAR', 'Y', 'YEARS', 'YR', 'YRS': The year field

  • 'YEAROFWEEK': The ISO 8601 week-numbering year that the datetime falls in. For example, 2005-01-02 is part of the 53rd week of year 2004, so the result is 2004

  • 'QUARTER', 'QTR': The quarter (1 - 4) of the year that the datetime falls in

  • 'MONTH', 'MON', 'MONS', 'MONTHS': The month field (1 - 12)

  • 'WEEK', 'W', 'WEEKS': The number of the ISO 8601 week-of-week-based-year. A week is considered to start on a Monday and week 1 is the first week with >3 days. In the ISO week-numbering system, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year. For example, 2005-01-02 is part of the 53rd week of year 2004, while 2012-12-31 is part of the first week of 2013

  • 'DAY', 'D', 'DAYS': The day of the month field (1 - 31)

  • 'DAYOFWEEK', 'DOW': The day of the week for datetime as Sunday(1) to Saturday(7)

  • 'DAYOFWEEK_ISO', 'DOW_ISO': ISO 8601 based day of the week for datetime as Monday(1) to Sunday(7)

  • 'DOY': The day of the year (1 - 365/366)

  • 'HOUR', 'H', 'HOURS', 'HR', 'HRS': The hour field (0 - 23)

  • 'MINUTE', 'M', 'MIN', 'MINS', 'MINUTES': The minutes field (0 - 59)

  • 'SECOND', 'S', 'SEC', 'SECONDS', 'SECS': The seconds field, including fractional parts

Supported values of field when source is INTERVAL are (case-insensitive):

  • 'YEAR', 'Y', 'YEARS', 'YR', 'YRS': The total months / 12

  • 'MONTH', 'MON', 'MONS', 'MONTHS': The total months % 12

  • 'DAY', 'D', 'DAYS': The days part of interval

  • 'HOUR', 'H', 'HOURS', 'HR', 'HRS': How many hours the microseconds contains

  • 'MINUTE', 'M', 'MIN', 'MINS', 'MINUTES': How many minutes left after taking hours from microseconds

  • 'SECOND', 'S', 'SEC', 'SECONDS', 'SECS': How many seconds with fractions left after taking hours and minutes from microseconds

The date_part function is a synonym for the SQL standard extract function.

For example date_part('year', CURRENT_DATE) is equivalent to extract(YEAR FROM CURRENT_DATE)

Examples 

> SELECT date_part('YEAR', TIMESTAMP'2019-08-12 01:00:00.123456');
 2019

> SELECT date_part('Week', TIMESTAMP'2019-08-12 01:00:00.123456');
 33

> SELECT date_part('day', DATE'2019-08-12');
 224

> SELECT date_part('SECONDS', TIMESTAMP'2019-10-01 00:00:01.000001');
 1.000001

> SELECT date_part('Months', INTERVAL '2-11' YEAR TO MONTH);
 11

> SELECT date_part('seconds', INTERVAL '5:00:30.001' HOUR TO SECOND);
 30.001000