描述

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

语法

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

参数

举例

1. 按整年取整

   SQL 复制

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

   Plain Text 复制

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

   SQL 复制

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

   Plain Text 复制

   1+-------------------------------------------------+
   2| year_floor(cast('2023-07-13' as DATETIMEV2(0))) |
   3+-------------------------------------------------+
   4| 2023-01-01 00:00:00                             |
   5+-------------------------------------------------+

2. 以 origin 为基准取整

   SQL 复制

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

   Plain Text 复制

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

3. 以 period 为单位取整

   SQL 复制

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

   Plain Text 复制

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

4. 以 origin 和 period 取整

   SQL 复制

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

   Plain Text 复制

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

   SQL 复制

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

   Plain Text 复制

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