YEAR_FLOOR
Description
It is used to round the given date down to the specified year interval starting point. It supports multiple variants, which can specify the starting time (origin) and period (period) in different ways to round.
Syntax
YEAR_FLOOR(<date_value>, [<period> | <origin_date_value>])
YEAR_FLOOR(<date_value>, <period>, <origin_date_value>)
Parameters
Parameter | Type | Description |
---|---|---|
<date_value> | DATE , DATETIME | The DATE or DATETIME input value to be rounded. |
<origin_date_value> | DATE , DATETIME | The DATE or DATETIME input value used as the reference point. If not provided, the default is 0001-01-01T00:00:00 . |
<period> | INT | The rounding interval, a positive integer indicating the number of years per cycle. |
Example
-
Rounding to the whole year
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 |
+-------------------------------------------------+ -
Round based on 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 |
+-----------------------------------------------------------------------------------------------+ -
Rounding with period as unit
SELECT YEAR_FLOOR('2023-07-13', 5);
+----------------------------------------------------+
| year_floor(cast('2023-07-13' as DATETIMEV2(0)), 5) |
+----------------------------------------------------+
| 2020-01-01 00:00:00 |
+----------------------------------------------------+ -
Round origin and 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 |
+-----------------------------------------------------------------------------------------+