类别：

日期和时间函数

YEAR* / DAY* / WEEK* / MONTH / QUARTER

从日期或时间戳中提取相应的日期部分。

这些函数是将 DATE_PART （或 EXTRACT）函数与等效日期部分一起使用的替代方法（请参阅 支持的日期和时间部分）。

另请参阅：

HOUR / MINUTE / SECOND

语法

YEAR( <date_or_timestamp_expr> )

YEAROFWEEK( <date_or_timestamp_expr> )
YEAROFWEEKISO( <date_or_timestamp_expr> )

DAY( <date_or_timestamp_expr> )

DAYOFMONTH( <date_or_timestamp_expr> )
DAYOFWEEK( <date_or_timestamp_expr> )
DAYOFWEEKISO( <date_or_timestamp_expr> )
DAYOFYEAR( <date_or_timestamp_expr> )

WEEK( <date_or_timestamp_expr> )

WEEKOFYEAR( <date_or_timestamp_expr> )
WEEKISO( <date_or_timestamp_expr> )

MONTH( <date_or_timestamp_expr> )

QUARTER( <date_or_timestamp_expr> )
Copy

使用说明

函数名称

从输入日期/时间戳中提取的日期部分

可能的值

YEAR

Year

任何有效年份（例如 2017）

YEAROFWEEK [1]

提取的周所属的年份

任何有效年份（例如 2017）

YEAROFWEEKISO

与 YEAROFWEEK 相同，但使用 ISO 语义

任何有效年份（例如 2017）

DAY、DAYOFMONTH

月中某日（数字）

1 至 31

DAYOFWEEK [1]

周中某日（数字）

0 至 7

DAYOFWEEKISO

与 DAYOFWEEK 相同，但使用 ISO 语义

1 至 7

DAYOFYEAR

年中某日（数字）

1 至 366

WEEK , WEEKOFYEAR [1]

年中某周（数字）

1 至 54

WEEKISO

与 WEEK 相同，但使用 ISO 语义

1 至 53

MONTH

年中某月（数字）

1 至 12

QUARTER

年中某季（数字）

1 至 4

[1] 结果由为 WEEK_OF_YEAR_POLICY 和/或 WEEK_START 会话参数设置的值决定。

有关 ISO 语义和参数的详细信息，请参阅 日历周和工作日。

示例

下方展示了函数 YEAR、MONTH、DAY、DAYOFWEEK、DAYOFYEAR 和 QUARTER 的用法。

SELECT 
       '2013-05-08T23:39:20.123-07:00'::TIMESTAMP AS tstamp,
       YEAR(tstamp) AS "YEAR", 
       QUARTER(tstamp) AS "QUARTER OF YEAR",
       MONTH(tstamp) AS "MONTH", 
       DAY(tstamp) AS "DAY",
       DAYOFMONTH(tstamp) AS "DAY OF MONTH",
       DAYOFYEAR(tstamp) AS "DAY OF YEAR";
+-------------------------+------+-----------------+-------+-----+--------------+-------------+
| TSTAMP                  | YEAR | QUARTER OF YEAR | MONTH | DAY | DAY OF MONTH | DAY OF YEAR |
|-------------------------+------+-----------------+-------+-----+--------------+-------------|
| 2013-05-08 23:39:20.123 | 2013 |               2 |     5 |   8 |            8 |         128 |
+-------------------------+------+-----------------+-------+-----+--------------+-------------+
Copy

下方展示了函数 WEEK、WEEKISO、WEEKOFYEAR、YEAROFWEEK 和 YEAROFWEEKISO 的用法。会话参数 WEEK_OF_YEAR_POLICY 设置为指示年中的第一周是包含该年 1 月 1 日的周。

ALTER SESSION SET WEEK_OF_YEAR_POLICY = 1;
Copy
SELECT 
       '2016-01-02T23:39:20.123-07:00'::TIMESTAMP AS tstamp,
       WEEK(tstamp) AS "WEEK",
       WEEKISO(tstamp) AS "WEEK ISO",
       WEEKOFYEAR(tstamp) AS "WEEK OF YEAR",
       YEAROFWEEK(tstamp) AS "YEAR OF WEEK",
       YEAROFWEEKISO(tstamp) AS "YEAR OF WEEK ISO"
       ;
+-------------------------+------+----------+--------------+--------------+------------------+
| TSTAMP                  | WEEK | WEEK ISO | WEEK OF YEAR | YEAR OF WEEK | YEAR OF WEEK ISO |
|-------------------------+------+----------+--------------+--------------+------------------|
| 2016-01-02 23:39:20.123 |    1 |       53 |            1 |         2016 |             2015 |
+-------------------------+------+----------+--------------+--------------+------------------+
Copy

下方展示了函数 WEEK、WEEKISO、WEEKOFYEAR、YEAROFWEEK 和 YEAROFWEEKISO 的用法。会话参数 WEEK_OF_YEAR_POLICY 设置为指示年中的第一周是包含该年至少 4 天的年中第一周。（例如，2010 年 12 月 26 日至 2011 年 1 月 1 日这一周被视为 2010 年的最后一周，而不是 2011 年的第一周，因为尽管该周包含 2011 年 1 月 1 日，但有小半周在 2011 年中。）

ALTER SESSION SET WEEK_OF_YEAR_POLICY = 0;
Copy
SELECT 
       '2016-01-02T23:39:20.123-07:00'::TIMESTAMP AS tstamp,
       WEEK(tstamp) AS "WEEK",
       WEEKISO(tstamp) AS "WEEK ISO",
       WEEKOFYEAR(tstamp) AS "WEEK OF YEAR",
       YEAROFWEEK(tstamp) AS "YEAR OF WEEK",
       YEAROFWEEKISO(tstamp) AS "YEAR OF WEEK ISO"
       ;
+-------------------------+------+----------+--------------+--------------+------------------+
| TSTAMP                  | WEEK | WEEK ISO | WEEK OF YEAR | YEAR OF WEEK | YEAR OF WEEK ISO |
|-------------------------+------+----------+--------------+--------------+------------------|
| 2016-01-02 23:39:20.123 |   53 |       53 |           53 |         2015 |             2015 |
+-------------------------+------+----------+--------------+--------------+------------------+
Copy

下方展示了函数 DAYOFWEEK 和 DAYOFWEEKISO 的用法。会话参数 WEEK_START 设置为指示一周从星期日开始。

ALTER SESSION SET WEEK_START = 7;
Copy
SELECT 
       '2016-01-02T23:39:20.123-07:00'::TIMESTAMP AS tstamp,
       DAYOFWEEK(tstamp) AS "DAY OF WEEK",
       DAYOFWEEKISO(tstamp) AS "DAY OF WEEK ISO"
       ;
+-------------------------+-------------+-----------------+
| TSTAMP                  | DAY OF WEEK | DAY OF WEEK ISO |
|-------------------------+-------------+-----------------|
| 2016-01-02 23:39:20.123 |           7 |               6 |
+-------------------------+-------------+-----------------+
Copy

下方展示了函数 DAYOFWEEK 和 DAYOFWEEKISO 的用法。会话参数 WEEK_START 设置为指示一周从星期一开始。

ALTER SESSION SET WEEK_START = 0;
Copy
SELECT 
       '2016-01-02T23:39:20.123-07:00'::TIMESTAMP AS tstamp,
       DAYOFWEEK(tstamp) AS "DAY OF WEEK",
       DAYOFWEEKISO(tstamp) AS "DAY OF WEEK ISO"
       ;
+-------------------------+-------------+-----------------+
| TSTAMP                  | DAY OF WEEK | DAY OF WEEK ISO |
|-------------------------+-------------+-----------------|
| 2016-01-02 23:39:20.123 |           6 |               6 |
+-------------------------+-------------+-----------------+
Copy

有关更多示例，请参阅 使用日期和时间值。

有关周相关的函数（DAYOFWEEK、WEEK、WEEKOFYEAR、YEAROFWEEK 等）的更详细示例，请参阅 日历周和工作日。

语言: 中文