YEAR_FLOOR

描述

用于将给定的日期向下取整到指定的年份间隔起点。它支持多个变体，可按不同方式指定起始时间 (origin) 和周期 (period) 进行取整。

语法

YEAR_FLOOR(<date_value>, [<period> | <origin_date_value>])
YEAR_FLOOR(<date_value>, <period>, <origin_date_value>)

参数

参数	类型	说明
`<date_value>`	`DATE`, `DATETIME`	需要取整的 `DATE` 或 `DATETIME` 输入值。
`<origin_date_value>`	`DATE`, `DATETIME`	用作基准的 `DATE` 或 `DATETIME` 输入值，如果不填，默认值为 `0001-01-01T00:00:00`。
`<period>`	`INT`	取整的时间间隔，正整数，表示以多少年为周期进行取整。

举例

按整年取整

SELECT YEAR_FLOOR('2023-07-13 22:28:18');

+----------------------------------------------------------+
| year_floor(cast('2023-07-13 22:28:18' as DATETIMEV2(0))) |
+----------------------------------------------------------+
| 2023-01-01 00:00:00                                      |
+----------------------------------------------------------+

 SELECT YEAR_FLOOR('2023-07-13');

+-------------------------------------------------+
| year_floor(cast('2023-07-13' as DATETIMEV2(0))) |
+-------------------------------------------------+
| 2023-01-01 00:00:00                             |
+-------------------------------------------------+

以 origin 为基准取整

 SELECT YEAR_FLOOR('2023-07-13 22:28:18', '2020-03-15');

+-----------------------------------------------------------------------------------------------+
| year_floor(cast('2023-07-13 22:28:18' as DATETIMEV2(0)), cast('2020-03-15' as DATETIMEV2(0))) |
+-----------------------------------------------------------------------------------------------+
| 2023-03-15 00:00:00                                                                           |
+-----------------------------------------------------------------------------------------------+

以 period 为单位取整

 SELECT YEAR_FLOOR('2023-07-13', 5);

+----------------------------------------------------+
| year_floor(cast('2023-07-13' as DATETIMEV2(0)), 5) |
+----------------------------------------------------+
| 2020-01-01 00:00:00                                |
+----------------------------------------------------+

以 origin 和 period 取整

SELECT YEAR_FLOOR('2023-07-13 22:28:18', 5, '2018-06-01');

+--------------------------------------------------------------------------------------------------+
| year_floor(cast('2023-07-13 22:28:18' as DATETIMEV2(0)), 5, cast('2018-06-01' as DATETIMEV2(0))) |
+--------------------------------------------------------------------------------------------------+
| 2023-06-01 00:00:00                                                                              |
+--------------------------------------------------------------------------------------------------+

 SELECT YEAR_FLOOR('2023-07-13', 5, '2016-01-01');

+-----------------------------------------------------------------------------------------+
| year_floor(cast('2023-07-13' as DATETIMEV2(0)), 5, cast('2016-01-01' as DATETIMEV2(0))) |
+-----------------------------------------------------------------------------------------+
| 2021-01-01 00:00:00                                                                     |
+-----------------------------------------------------------------------------------------+

YEAR_CEIL YEAR