跳到主要内容

YEAR_FLOOR

描述

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

语法

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

参数

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

举例

  1. 按整年取整

    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 |
    +-------------------------------------------------+
  2. 以 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 |
    +-----------------------------------------------------------------------------------------------+
  3. 以 period 为单位取整

     SELECT YEAR_FLOOR('2023-07-13', 5);
    +----------------------------------------------------+
    | year_floor(cast('2023-07-13' as DATETIMEV2(0)), 5) |
    +----------------------------------------------------+
    | 2020-01-01 00:00:00 |
    +----------------------------------------------------+
  4. 以 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 |
    +-----------------------------------------------------------------------------------------+