date_part
function
Applies to: Databricks SQL Databricks Runtime
Extracts a part of the date, timestamp, or interval.
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