This is an automated email from the ASF dual-hosted git repository.
zclll pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new 47efe5136a9 [fix](doc) Fix date functions years (#2820)
47efe5136a9 is described below
commit 47efe5136a93e2a8bc487feeb05c90bc3e7b7f87
Author: dwdwqfwe <[email protected]>
AuthorDate: Thu Sep 25 17:21:17 2025 +0800
[fix](doc) Fix date functions years (#2820)
## Versions
- [x] dev
- [ ] 3.0
- [ ] 2.1
- [ ] 2.0
## Languages
- [x] Chinese
- [x] English
## Docs Checklist
- [ ] Checked by AI
- [ ] Test Cases Built
---
.../date-time-functions/year-ceil.md | 138 ++++++++++---
.../date-time-functions/year-floor.md | 217 +++++++++++++--------
.../date-time-functions/year-of-week.md | 103 +++++++---
.../scalar-functions/date-time-functions/year.md | 58 ++++--
.../date-time-functions/years-add.md | 88 +++++++--
.../date-time-functions/years-diff.md | 86 ++++++--
.../date-time-functions/years-sub.md | 79 ++++++--
.../date-time-functions/yearweek.md | 125 +++++++-----
.../date-time-functions/year-ceil.md | 134 ++++++++++---
.../date-time-functions/year-floor.md | 209 +++++++++++++-------
.../date-time-functions/year-of-week.md | 103 +++++++---
.../scalar-functions/date-time-functions/year.md | 54 +++--
.../date-time-functions/years-add.md | 84 ++++++--
.../date-time-functions/years-diff.md | 82 ++++++--
.../date-time-functions/years-sub.md | 73 +++++--
.../date-time-functions/yearweek.md | 99 ++++++----
16 files changed, 1285 insertions(+), 447 deletions(-)
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year-ceil.md
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year-ceil.md
index 5d204b560bc..92d0a88a641 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year-ceil.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year-ceil.md
@@ -1,43 +1,127 @@
---
{
- "title": "YEAR_CEIL",
- "language": "en"
+ "title": "YEAR_CEIL",
+ "language": "en"
}
---
-## year_ceil
-### description
-#### Syntax
+## Description
+The YEAR_CEIL function rounds up an input datetime value to the nearest
specified year interval start time, with the interval unit being year. If a
starting reference point (origin) is specified, it uses that point as the basis
for calculating intervals; otherwise, it defaults to using 0000-01-01 00:00:00
as the reference point.
+
+Date calculation formula:
+$$
+\text{YEAR\_CEIL}(\langle\text{date\_or\_time\_expr}\rangle,
\langle\text{period}\rangle, \langle\text{origin}\rangle) =
\min\{\langle\text{origin}\rangle + k \times \langle\text{period}\rangle \times
\text{year} \mid k \in \mathbb{Z} \land \langle\text{origin}\rangle + k \times
\langle\text{period}\rangle \times \text{year} \geq
\langle\text{date\_or\_time\_expr}\rangle\}
+$$
+where K represents the number of periods needed to reach the target time from
the reference time.
+
+## Syntax
```sql
-DATETIME YEAR_CEIL(DATETIME datetime)
-DATETIME YEAR_CEIL(DATETIME datetime, DATETIME origin)
-DATETIME YEAR_CEIL(DATETIME datetime, INT period)
-DATETIME YEAR_CEIL(DATETIME datetime, INT period, DATETIME origin)
+YEAR_CEIL(<date_or_time_expr>)
+YEAR_CEIL(<date_or_time_expr>, origin)
+YEAR_CEIL(<date_or_time_expr>, <period>)
+YEAR_CEIL(<date_or_time_expr>, <period>, <origin>)
```
+## Parameters
-Convert the date to the nearest rounding up time of the specified time
interval period.
+| Parameter | Description |
+|-----------|-------------|
+| `<date_or_time_expr>` | The datetime value to round up, supports
date/datetime types. For datetime and date formats, please refer to [datetime
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
+| `<period>` | Optional, represents how many seconds each period consists of,
supports positive integer type (INT). Default is 1 second. |
+| `<origin_datetime>` | Starting point for the interval, supports
date/datetime types; defaults to 0000-01-01 00:00:00. |
-- datetime: a valid date expression.
-- period: specifies how many years each cycle consists of.
-- origin: starting from 0001-01-01T00:00:00.
+## Return Value
-### example
+Returns a result consistent with the input type (DATETIME or DATE),
representing the year interval start time after rounding up:
-```
-mysql> select year_ceil("2023-07-13 22:28:18", 5);
-+------------------------------------------------------------+
-| year_ceil(cast('2023-07-13 22:28:18' as DATETIMEV2(0)), 5) |
-+------------------------------------------------------------+
-| 2025-01-01 00:00:00 |
-+------------------------------------------------------------+
-1 row in set (0.02 sec)
-```
+- If input is DATE type, returns DATE type (containing only date part); if
input is DATETIME or properly formatted string, returns DATETIME type (time
part consistent with origin, defaults to 00:00:00 when no origin).
+- If `<period>` is a non-positive integer (≤0), the function returns an error.
+- If any parameter is NULL, returns NULL.
+- If `<date_or_time_expr>` is exactly at an interval start point (based on
`<period>` and `<origin>`), returns that start point.
+- If calculation result exceeds maximum datetime 9999-12-31 23:59:59, returns
an error.
+- If the `<origin>` date and time is after the `<period>`, it will still be
calculated according to the above formula, but the period k will be negative.
+- If date_or_time_expr has a scale, the returned result will also have a scale
with the fractional part being zero.
+
+## Examples
+
+```sql
+-- Default 1-year interval (start point is January 1st each year), 2023-07-13
rounds up to 2024-01-01
+SELECT YEAR_CEIL('2023-07-13 22:28:18') AS result;
++---------------------+
+| result |
++---------------------+
+| 2024-01-01 00:00:00 |
++---------------------+
-### keywords
+-- Specify 5-year interval, 2023-07-13 rounds up to nearest 5-year interval
start (calculated with default origin)
+SELECT YEAR_CEIL('2023-07-13 22:28:18', 5) AS result;
++---------------------+
+| result |
++---------------------+
+| 2025-01-01 00:00:00 |
++---------------------+
- YEAR_CEIL, YEAR, CEIL
+-- Input is DATE type, returns DATE type interval start
+SELECT YEAR_CEIL(cast('2023-07-13' as date)) AS result;
++------------+
+| result |
++------------+
+| 2024-01-01 |
++------------+
-### Best Practice
+-- Specify origin reference point='2020-01-01', 1-year interval, 2023-07-13
rounds to 2024-01-01
+SELECT YEAR_CEIL('2023-07-13', 1, '2020-01-01') AS result;
++---------------------+
+| result |
++---------------------+
+| 2024-01-01 00:00:00 |
++---------------------+
-See also [date_ceil](./date-ceil)
+---input with scale
+mysql> SELECT YEAR_CEIL('2023-07-13 22:28:18.123', 5) AS result;
++-------------------------+
+| result |
++-------------------------+
+| 2026-01-01 00:00:00.000 |
++-------------------------+
+
+-- Specify origin with time part, returned result's time part matches origin
+SELECT YEAR_CEIL('2023-07-13', 1, '2020-01-01 08:30:00') AS result;
++---------------------+
+| result |
++---------------------+
+| 2024-01-01 08:30:00 |
++---------------------+
+
+-- Input exactly at interval start point (origin='2023-01-01', period=1),
returns itself
+SELECT YEAR_CEIL('2023-01-01', 1, '2023-01-01') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 00:00:00 |
++---------------------+
+
+--- If the <origin> date and time is after the <period>, it will still be
calculated according to the above formula, but the period k will be negative.
+SELECT YEAR_CEIL('2023-07-13 22:22:56', 1, '2028-01-01 08:30:00') AS result;
++---------------------+
+| result |
++---------------------+
+| 2024-01-01 08:30:00 |
++---------------------+
+
+-- Invalid period (non-positive integer)
+SELECT YEAR_CEIL('2023-07-13', 0) AS result;
+ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation
year_ceil of 2023-07-13 00:00:00, 0 out of range
+
+-- Any parameter is NULL, returns NULL
+SELECT YEAR_CEIL(NULL, 1) AS result;
++--------+
+| result |
++--------+
+| NULL |
++--------+
+
+-- Calculation result exceeds maximum datetime, returns error
+SELECT YEAR_CEIL('9999-12-31 22:28:18', 5) AS result;
+-- ERROR: Operation out of range
+```
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year-floor.md
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year-floor.md
index a26c1c99bfa..1dcb04a544a 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year-floor.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year-floor.md
@@ -6,88 +6,147 @@
---
## 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.
+The YEAR_FLOOR function rounds down an input datetime value to the nearest
specified year interval start time, with the interval unit being year. If a
starting reference point (origin) is specified, it uses that point as the basis
for calculating intervals; otherwise, it defaults to using 0000-01-01 00:00:00
as the reference point.
-## Syntax
+Date calculation formula:
+$$
+\text{YEAR\_FLOOR}(\langle\text{date\_or\_time\_expr}\rangle,
\langle\text{period}\rangle, \langle\text{origin}\rangle) =
\max\{\langle\text{origin}\rangle + k \times \langle\text{period}\rangle \times
\text{year} \mid k \in \mathbb{Z} \land \langle\text{origin}\rangle + k \times
\langle\text{period}\rangle \times \text{year} \leq
\langle\text{date\_or\_time\_expr}\rangle\}
+$$
+where K represents the number of periods from the reference time to the target
time.
+## Syntax
```sql
-YEAR_FLOOR(<date_value>, [<period> | <origin_date_value>])
-YEAR_FLOOR(<date_value>, <period>, <origin_date_value>)
+YEAR_FLOOR(<date_or_time_expr>)
+YEAR_FLOOR(<date_or_time_expr>, origin)
+YEAR_FLOOR(<date_or_time_expr>, <period>)
+YEAR_FLOOR(<date_or_time_expr>, <period>, <origin>)
```
## 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
-1. Rounding to the whole year
- ```sql
- 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 |
- +----------------------------------------------------------+
- ```
- ```sql
- SELECT YEAR_FLOOR('2023-07-13');
- ```
- ```
- +-------------------------------------------------+
- | year_floor(cast('2023-07-13' as DATETIMEV2(0))) |
- +-------------------------------------------------+
- | 2023-01-01 00:00:00 |
- +-------------------------------------------------+
- ```
-
-2. Round based on origin
- ```sql
- 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. Rounding with period as unit
- ```sql
- SELECT YEAR_FLOOR('2023-07-13', 5);
- ```
- ```
- +----------------------------------------------------+
- | year_floor(cast('2023-07-13' as DATETIMEV2(0)), 5) |
- +----------------------------------------------------+
- | 2020-01-01 00:00:00 |
- +----------------------------------------------------+
- ```
-
-4. Round origin and period
- ```sql
- 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
|
-
+--------------------------------------------------------------------------------------------------+
- ```
- ```sql
- 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
|
-
+-----------------------------------------------------------------------------------------+
- ```
+| Parameter | Description |
+|-----------|-------------|
+| `<date_or_time_expr>` | The datetime value to round down, supports
date/datetime types. For datetime and date formats, please refer to [datetime
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
+| `<period>` | Optional, represents how many seconds each period consists of,
supports positive integer type (INT). Default is 1 second. |
+| `<origin_datetime>` | Starting point for the interval, supports
date/datetime types; defaults to 0000-01-01 00:00:00. |
+
+## Return Value
+Returns a result consistent with the input type (DATETIME or DATE),
representing the year interval start time after rounding down:
+
+- If input is DATE type, returns DATE type (containing only date part); if
input is DATETIME or properly formatted string, returns DATETIME type (time
part consistent with origin, defaults to 00:00:00 when no origin).
+- If `<period>` is a non-positive integer (≤0), the function returns an error.
+- If any parameter is NULL, returns NULL.
+- If `<date_or_time_expr>` is exactly at an interval start point (based on
`<period>` and `<origin>`), returns that start point.
+- If calculation result exceeds maximum datetime 9999-12-31 23:59:59, returns
an error.
+- If the `<origin>` date and time is after the `<period>`, it will still be
calculated according to the above formula, but the period k will be negative.
+- If date_or_time_expr has a scale, the returned result will also have a scale
with the fractional part being zero.
+
+## Examples
+```sql
+-- Default 1-year interval (start point is January 1st each year), 2023-07-13
rounds down to 2023-01-01
+SELECT YEAR_FLOOR('2023-07-13 22:28:18') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 00:00:00 |
++---------------------+
+
+-- Specify 5-year interval, 2023-07-13 rounds down to nearest 5-year interval
start (calculated with default origin)
+SELECT YEAR_FLOOR('2023-07-13 22:28:18', 5) AS result;
++---------------------+
+| result |
++---------------------+
+| 2020-01-01 00:00:00 |
++---------------------+
+
+---input with scale
+mysql> SELECT YEAR_FLOOR('2023-07-13 22:28:18.123', 5) AS result;
++-------------------------+
+| result |
++-------------------------+
+| 2021-01-01 00:00:00.000 |
++-------------------------+
+
+-- Input is DATE type, returns DATE type interval start
+SELECT YEAR_FLOOR(cast('2023-07-13' as date)) AS result;
++------------+
+| result |
++------------+
+| 2023-01-01 |
++------------+
+
+-- Specify origin reference point='2020-01-01', 1-year interval, 2023-07-13
rounds to 2023-01-01
+SELECT YEAR_FLOOR('2023-07-13', 1, '2020-01-01') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 00:00:00 |
++---------------------+
+
+-- Specify origin with time part, returned result's time part matches origin
+SELECT YEAR_FLOOR('2023-07-13', 1, '2020-01-01 08:30:00') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 08:30:00 |
++---------------------+
+
+-- Input exactly at interval start point (origin='2023-01-01', period=1),
returns itself
+SELECT YEAR_FLOOR('2023-01-01', 1, '2023-01-01') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 00:00:00 |
++---------------------+
+
+-- Input time earlier than origin start time, rounds down to earlier interval
point
+SELECT YEAR_FLOOR('2019-07-13', 1, '2020-01-01') AS result;
++---------------------+
+| result |
++---------------------+
+| 2019-01-01 00:00:00 |
++---------------------+
+
+-- Cross-multiple periods rounding down, period=3, origin='2020-01-01'
+SELECT YEAR_FLOOR('2025-07-13', 3, '2020-01-01') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 00:00:00 |
++---------------------+
+
+--- If the <origin> date and time is after the <period>, it will still be
calculated according to the above formula, but the period k will be negative.
+SELECT YEAR_FLOOR('2023-07-13 22:22:56', 1, '2028-01-01 08:30:00') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 08:30:00 |
++---------------------+
+
+-- Input time part earlier than origin time part, rounds down within same year
+SELECT YEAR_FLOOR('2023-07-13 06:00:00', 1, '2020-01-01 08:30:00') AS result;
++---------------------+
+| result |
++---------------------+
+| 2022-01-01 08:30:00 |
++---------------------+
+
+-- Input time part later than origin time part, normal rounding down
+SELECT YEAR_FLOOR('2023-07-13 10:00:00', 1, '2020-01-01 08:30:00') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 08:30:00 |
++---------------------+
+
+-- Invalid period (non-positive integer)
+SELECT YEAR_FLOOR('2023-07-13', 0) AS result;
+ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation
year_floor of 2023-07-13 00:00:00, 0 out of range
+
+-- Any parameter is NULL, returns NULL
+SELECT YEAR_FLOOR(NULL, 1) AS result;
++--------+
+| result |
++--------+
+| NULL |
++--------+
+```
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year-of-week.md
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year-of-week.md
index 6701ba882e2..f994c6293cc 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year-of-week.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year-of-week.md
@@ -5,44 +5,103 @@
}
---
-## year
+## Description
-year_of_week
+The YEAR_OF_WEEK function returns the week-year (year of week) for a specified
date according to the ISO 8601 week calendar standard. Unlike regular years,
ISO week-years are calculated in week units, where the first week of a year is
the week containing January 4th, and that week must contain at least 4 days
belonging to that year.
-## Description
+Unlike the [year function](./year), which simply returns the year of the input
date, YEAR_OF_WEEK follows the ISO week calendar standard.
-Return to the `ISO week date` standard year, please refer to [ISO Week
date](https://en.wikipedia.org/wiki/ISO_week_date).
+For more detailed information, please refer to [ISO Week
Date](https://en.wikipedia.org/wiki/ISO_week_date).
## Alias
-- yow
+- `YOW`
## Syntax
```sql
-SMALLINT year_of_week(DATE value)
+YEAR_OF_WEEK(`<date_or_time_expr>`)
+YOW(`<date_or_time_expr>`)
```
## Parameters
-| Parameters | Description |
-| -- | -- |
-| `<value>` | A date for calculate the year of week |
+| Parameter | Description |
+|-----------|-------------|
+| `<date_or_time_expr>` | Input datetime value, supports date/datetime types.
For datetime and date formats, please refer to [datetime
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/date-conversion)|
-## 返回值
+## Return Value
-Return to the `ISO week date` standard year
+Returns SMALLINT type, representing the week-year calculated according to ISO
8601 week calendar standard.
-## example
+- Return value range is typically 1-9999
+- If input is NULL, returns NULL
+- If input is DATETIME type, only considers the date part, ignoring the time
part
-```
-mysql> select year_of_week('2005-01-01');
-+-----------------------------+
-| year_of_week('2005-01-01') |
-+-----------------------------+
-| 2004 |
-+-----------------------------+
-```
+## Examples
+
+```sql
+-- 2005-01-01 is Saturday, this week starts from 2004-12-27, contains more
days in 2004, belongs to 2004
+SELECT YEAR_OF_WEEK('2005-01-01') AS yow_result;
++------------+
+| yow_result |
++------------+
+| 2004 |
++------------+
+
+-- Using alias YOW, same result
+SELECT YOW('2005-01-01') AS yow_alias_result;
++------------------+
+| yow_alias_result |
++------------------+
+| 2004 |
++------------------+
-### keywords
- YEAR_OF_WEEK
+-- 2005-01-03 is Monday, this week (2005-01-03 to 2005-01-09) is the first
week of 2005
+SELECT YEAR_OF_WEEK('2005-01-03') AS yow_result;
++------------+
+| yow_result |
++------------+
+| 2005 |
++------------+
+
+-- 2023-01-01 is Sunday, this week starts from 2022-12-26, belongs to the last
week of 2022
+SELECT YEAR_OF_WEEK('2023-01-01') AS yow_result;
++------------+
+| yow_result |
++------------+
+| 2022 |
++------------+
+
+-- 2023-01-02 is Monday, this week (2023-01-02 to 2023-01-08) is the first
week of 2023
+SELECT YEAR_OF_WEEK('2023-01-02') AS yow_result;
++------------+
+| yow_result |
++------------+
+| 2023 |
++------------+
+
+-- DATETIME type input, ignoring time part
+SELECT YEAR_OF_WEEK('2005-01-01 15:30:45') AS yow_datetime;
++--------------+
+| yow_datetime |
++--------------+
+| 2004 |
++--------------+
+
+-- Cross-year boundary case: 2024-12-30 is Monday, belongs to the first week
of 2025
+SELECT YEAR_OF_WEEK('2024-12-30') AS yow_result;
++------------+
+| yow_result |
++------------+
+| 2025 |
++------------+
+
+-- Input is NULL, returns NULL
+SELECT YEAR_OF_WEEK(NULL) AS yow_null;
++----------+
+| yow_null |
++----------+
+| NULL |
++----------+
+```
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year.md
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year.md
index 0f318e1e241..ae9f2f2f9a5 100644
--- a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year.md
+++ b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/year.md
@@ -5,26 +5,52 @@
}
---
-## year
-### Description
-#### Syntax
+## Description
+The YEAR function extracts the year part from a specified date or time value,
returning the year as an integer. It supports processing DATE and DATETIME
types.
-`INT YEAR(DATETIME date)`
+This function behaves consistently with the [year
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_year)
in MySQL.
+## Syntax
+```sql
+YEAR(`<date_or_time_expr>`)
+```
-Returns the year part of the date type, ranging from 1000 to 9999
+## Parameters
-The parameter is Date or Datetime type
+| Parameter | Description |
+|-----------|-------------|
+| `<date_or_time_expr>` | The datetime value to extract year from, supports
date/datetime types. For datetime and date formats, please refer to [datetime
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
-### example
+## Return Value
+Returns the year part of the date/datetime type, INT type, range from 0-9999.
+
+- If the input parameter is NULL, returns NULL
+
+## Examples
+
+```sql
+-- Extract year from DATE type
+SELECT YEAR('1987-01-01') AS year_date;
++-----------+
+| year_date |
++-----------+
+| 1987 |
++-----------+
+
+-- Extract year from DATETIME type (ignoring hours, minutes, seconds)
+SELECT YEAR('2024-05-20 14:30:25') AS year_datetime;
++---------------+
+| year_datetime |
++---------------+
+| 2024 |
++---------------+
+
+-- Input is NULL (returns NULL)
+SELECT YEAR(NULL) AS null_input;
++------------+
+| null_input |
++------------+
+| NULL |
++------------+
```
-mysql> select year('1987-01-01');
-+-----------------------------+
-| year('1987-01-01 00:00:00') |
-+-----------------------------+
-| 1987 |
-+-----------------------------+
-```
-### keywords
- YEAR
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/years-add.md
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/years-add.md
index 0312387fa26..aee16c36eef 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/years-add.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/years-add.md
@@ -7,35 +7,91 @@
## Description
-Returns a new datetime value that is the result of adding a specified number
of years to the input datetime.
+The YEARS_ADD function is used to add (or subtract) a specified number of
years to a given date or time value, returning the adjusted date or time. It
supports processing DATE and DATETIME types, where the number of years can be
positive (addition) or negative (subtraction).
+
+This function behaves consistently with the [date_add
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-add)
using YEAR as the unit in MySQL.
## Syntax
```sql
-YEARS_ADD(<date>, <years>)
+YEARS_ADD(`<date_or_time_expr>`, `<years>`)
```
## Parameters
-| Parameter | Description |
-|-----------|--------------------------------------------------|
-| `<date>` | The input datetime value, which can be of type DATETIME or
DATE |
-| `<years>` | The number of years to add, of type INT |
+| Parameter | Description |
+|-----------|-------------|
+| `<date_or_time_expr>` | Input datetime value, supports date/datetime types.
For datetime and date formats, please refer to [datetime
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
+| `<years>` | Number of years to add, type INT, negative numbers indicate
subtraction, positive numbers indicate addition |
+
## Return Value
-Returns a value with the same type as the input `<date>` (DATETIME or DATE),
representing the time value after adding the specified number of years to the
input datetime.
+Returns a result consistent with the input type (DATE or DATETIME),
representing the adjusted date or time:
+
+- If input is DATE type, return value remains DATE type (only adjusts year,
month, day).
+- If input is DATETIME type, return value remains DATETIME type (year, month,
day adjusted, hours, minutes, seconds remain unchanged).
+- `<years_value>` as negative number indicates subtracting years (equivalent
to YEARS_SUB(`<datetime_or_date_value>`, `<years_value>`)).
+- Any input parameter is NULL, returns NULL.
+- If calculation result exceeds valid date type range (0000-01-01 00:00:00 to
9999-12-31 23:59:59), returns error.
+- If the adjusted month has insufficient days (e.g., February 29th plus 1 year
and next year is not a leap year), automatically adjusts to the last day of
that month (e.g., 2020-02-29 plus 1 year returns 2021-02-28).
-## Example
+## Examples
```sql
-SELECT YEARS_ADD('2020-01-31 02:02:02', 1);
-```
+-- DATETIME type add 1 year (basic functionality, hours, minutes, seconds
remain unchanged)
+SELECT YEARS_ADD('2020-01-31 02:02:02', 1) AS add_1_year_datetime;
++-----------------------+
+| add_1_year_datetime |
++-----------------------+
+| 2021-01-31 02:02:02 |
++-----------------------+
+
+-- DATETIME type subtract 1 year (negative years_value, cross-year)
+SELECT YEARS_ADD('2023-05-10 15:40:20', -1) AS subtract_1_year_datetime;
++--------------------------+
+| subtract_1_year_datetime |
++--------------------------+
+| 2022-05-10 15:40:20 |
++--------------------------+
+
+-- DATE type add 3 years (only adjust date)
+SELECT YEARS_ADD('2019-12-25', 3) AS add_3_year_date;
++------------------+
+| add_3_year_date |
++------------------+
+| 2022-12-25 |
++------------------+
+
+-- Leap day handling (2020-02-29 add 1 year, next year is not leap year)
+SELECT YEARS_ADD('2020-02-29', 1) AS leap_day_adjust;
++------------------+
+| leap_day_adjust |
++------------------+
+| 2021-02-28 |
++------------------+
+
+-- Cross-month day adjustment (January 31st add 1 year to February)
+SELECT YEARS_ADD('2023-01-31', 1) AS month_day_adjust;
++------------------+
+| month_day_adjust |
++------------------+
+| 2024-01-31 | -- 2024 January has 31 days, no adjustment needed
++------------------+
+
+-- Input is NULL (returns NULL)
+SELECT YEARS_ADD(NULL, 5) AS null_input;
++------------+
+| null_input |
++------------+
+| NULL |
++------------+
+
+-- Calculation result exceeds datetime range (upper limit)
+SELECT YEARS_ADD('9999-12-31', 1);
+-- ERROR: Operation out of range
-```text
-+-------------------------------------+
-| years_add('2020-01-31 02:02:02', 1) |
-+-------------------------------------+
-| 2021-01-31 02:02:02 |
-+-------------------------------------+
+-- Calculation result exceeds datetime range (lower limit)
+SELECT YEARS_ADD('0000-01-01', -1);
+-- ERROR: Operation out of range
```
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/years-diff.md
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/years-diff.md
index e066ca6e325..6cc3182fe52 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/years-diff.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/years-diff.md
@@ -7,35 +7,89 @@
## Description
-Calculates the difference in years between two datetime values.
+The YEARS_DIFF function is used to calculate the complete year difference
between two date or time values, with the result being the number of years from
the start time to the end time. It supports processing DATE and DATETIME types,
and considers the complete time difference (including months, days, hours,
minutes, and seconds) when calculating.
## Syntax
```sql
-YEARS_DIFF(<enddate>, <startdate>)
+YEARS_DIFF(`<date_or_time_expr1>`, `<date_or_time_expr2>`)
```
## Parameters
-| Parameter | Description |
-|-----------|--------------------------------------------------|
-| `<enddate>` | The end date, which can be of type DATETIME or DATE |
-| `<startdate>` | The start date, which can be of type DATETIME or DATE |
+| Parameter | Description |
+|-----------|-------------|
+| `<date_or_time_expr1>` | End date, supports date/datetime types. For
datetime and date formats, please refer to [datetime
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
+| `<date_or_time_expr2>` | Start date, supports date/datetime types and
strings conforming to date-time format |
## Return Value
-Returns a value of type INT, representing the number of years between the two
dates.
+Returns an INT type integer representing the complete year difference between
`<date_or_time_expr1>` and `<date_or_time_expr2>`:
-## Example
+- If `<date_or_time_expr1>` is later than `<date_or_time_expr2>`, returns a
positive number (must satisfy "complete year" condition, e.g., '2022-03-15
08:30:00' and '2021-03-15 09:10:00' actually differ by less than a full year,
returns 0).
+- If `<date_or_time_expr1>` is earlier than `<date_or_time_expr2>`, returns a
negative number (calculation method same as above, result negated).
+- If input is DATE type, defaults its time part to 00:00:00.
+- If any parameter is NULL, returns NULL.
+- Special leap year February case (e.g., 2024 is a leap year, February 29th vs
February 28th 2023, constitutes a full year)
+
+## Examples
```sql
-SELECT YEARS_DIFF('2020-12-25', '2019-10-25');
-```
+-- Year difference of 1 year, and month-day equal (full year)
+SELECT YEARS_DIFF('2020-12-25', '2019-12-25') AS diff_full_year;
++----------------+
+| diff_full_year |
++----------------+
+| 1 |
++----------------+
+
+-- Year difference of 1 year, but end month-day earlier than start month-day
(less than a year)
+SELECT YEARS_DIFF('2020-11-25', '2019-12-25') AS diff_less_than_year;
++---------------------+
+| diff_less_than_year |
++---------------------+
+| 0 |
++---------------------+
+
+-- DATETIME type with time components
+SELECT YEARS_DIFF('2022-03-15 08:30:00', '2021-03-15 09:10:00') AS
diff_datetime;
++---------------+
+| diff_datetime |
++---------------+
+| 0 |
++---------------+
+
+-- Mixed DATE and DATETIME calculation, DATE type input defaults time part to
00:00:00
+SELECT YEARS_DIFF('2024-05-20', '2020-05-20 12:00:00') AS diff_mixed;
++------------+
+| diff_mixed |
++------------+
+| 3 |
++------------+
+
+-- End time earlier than start time, returns negative number
+SELECT YEARS_DIFF('2018-06-10', '2020-06-10') AS diff_negative;
++---------------+
+| diff_negative |
++---------------+
+| -2 |
++---------------+
+
+-- Special leap year February case (2024 is a leap year, February 29th vs
February 28th 2023, constitutes a full year)
+SELECT YEARS_DIFF('2024-02-29', '2023-02-28') AS leap_year_diff;
++----------------+
+| leap_year_diff |
++----------------+
+| 1 |
++----------------+
-```text
-+----------------------------------------------------------+
-| years_diff('2020-12-25 00:00:00', '2019-10-25 00:00:00') |
-+----------------------------------------------------------+
-| 1 |
-+----------------------------------------------------------+
+-- Any parameter is NULL (returns NULL)
+SELECT
+ YEARS_DIFF(NULL, '2023-03-15') AS null_input1,
+ YEARS_DIFF('2023-03-15', NULL) AS null_input2;
++-------------+-------------+
+| null_input1 | null_input2 |
++-------------+-------------+
+| NULL | NULL |
++-------------+-------------+
```
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/years-sub.md
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/years-sub.md
index b458beeeb35..e7b0064cd57 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/years-sub.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/years-sub.md
@@ -7,35 +7,82 @@
## Description
-Returns a new datetime value that is the result of subtracting a specified
number of years from the input datetime.
+The YEARS_SUB function is used to subtract (or add) a specified number of
years from a given date or time value, returning the adjusted date or time
(essentially subtracting years_value × 1 year). It supports processing DATE and
DATETIME types, where the number of years can be positive (subtraction) or
negative (addition).
+
+This function behaves consistently with the [date_sub
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-sub)
using YEAR as the unit in MySQL.
## Syntax
```sql
-YEARS_SUB(<date>, <years>)
+YEARS_SUB(`<date_or_time_expr>`, `<years>`)
```
## Parameters
-| Parameter | Description |
-|-----------|--------------------------------------------------|
-| `<date>` | The input datetime value, which can be of type DATETIME or
DATE |
-| `<years>` | The number of years to subtract, of type INT |
+| Parameter | Description |
+|-----------|-------------|
+| `<date_or_time_expr>` | Input datetime value, supports date/datetime types.
For datetime and date formats, please refer to [datetime
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
and [date
conversion](../../../../../docs/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
+| `<years>` | Number of years to subtract, type INT, positive numbers indicate
subtraction, negative numbers indicate addition |
## Return Value
-Returns a value with the same type as the input `<date>` (DATETIME or DATE),
representing the time value after subtracting the specified number of years
from the input datetime.
+Returns a result consistent with the input type (DATE or DATETIME),
representing the adjusted date or time:
+
+- If input is DATE type, return value remains DATE type (only adjusts year,
month, day).
+- If input is DATETIME type, return value remains DATETIME type (year, month,
day adjusted, hours, minutes, seconds remain unchanged).
+- `<years_value>` as negative number indicates adding years (equivalent to
YEARS_ADD(`<datetime_or_date_value>`, `<years_value>`)).
+- Any input parameter is NULL, returns NULL.
+- If calculation result exceeds valid date type range (0000-01-01 00:00:00 to
9999-12-31 23:59:59), returns error.
+- If the adjusted month has insufficient days (e.g., from leap year February
29th subtract 1 year to non-leap year February 28th), automatically adjusts to
the actual number of days in that month.
-## Example
+## Examples
```sql
-SELECT YEARS_SUB('2020-02-02 02:02:02', 1);
-```
+-- DATETIME type subtract 1 year (basic functionality, hours, minutes, seconds
remain unchanged)
+SELECT YEARS_SUB('2020-02-02 02:02:02', 1) AS sub_1_year_datetime;
++---------------------+
+| sub_1_year_datetime |
++---------------------+
+| 2019-02-02 02:02:02 |
++---------------------+
+
+-- DATETIME type add 1 year (negative years_value, cross-year)
+SELECT YEARS_SUB('2022-05-10 15:40:20', -1) AS add_1_year_datetime;
++---------------------+
+| add_1_year_datetime |
++---------------------+
+| 2023-05-10 15:40:20 |
++---------------------+
+
+-- DATE type subtract 3 years (only adjust date)
+SELECT YEARS_SUB('2022-12-25', 3) AS sub_3_year_date;
++-----------------+
+| sub_3_year_date |
++-----------------+
+| 2019-12-25 |
++-----------------+
+
+-- Leap day handling (from leap year February 29th subtract 1 year to non-leap
year February 28th)
+SELECT YEARS_SUB('2020-02-29', 1) AS leap_day_adjust_1;
++-------------------+
+| leap_day_adjust_1 |
++-------------------+
+| 2019-02-28 |
++-------------------+
+
+-- Input is NULL (returns NULL)
+SELECT YEARS_SUB(NULL, 5) AS null_input;
++------------+
+| null_input |
++------------+
+| NULL |
++------------+
+
+-- Calculation result exceeds datetime range (upper limit)
+SELECT YEARS_SUB('9999-12-31', -1);
+ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation
year_add of 9999-12-31, 1 out of range
-```text
-+-------------------------------------+
-| years_sub('2020-02-02 02:02:02', 1) |
-+-------------------------------------+
-| 2019-02-02 02:02:02 |
-+-------------------------------------+
+-- Calculation result exceeds datetime range (lower limit)
+SELECT YEARS_SUB('0000-01-01', 1);
+ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation
year_add of 0000-01-01, -1 out of range
```
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/yearweek.md
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/yearweek.md
index f3e8d139d8b..3b7467c48d3 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/yearweek.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/yearweek.md
@@ -5,56 +5,91 @@
}
---
-## yearweek
-### Description
-#### Syntax
+## Description
-`INT YEARWEEK(DATE date[, INT mode])`
+The YEARWEEK function is used to return the "year + week number" combination
for a specified date (format YYYYWW, e.g., 202301 represents week 1 of 2023).
This function flexibly defines the start day of the week and the criteria for
determining the "first week" through the optional parameter mode, defaulting to
mode=0.
-Returns year and week for a date.The value of the mode argument defaults to 0.
-When the week of the date belongs to the previous year, the year and week of
the previous year are returned;
-when the week of the date belongs to the next year, the year of the next year
is returned and the week is 1.
+Week numbers range from 1-53, depending on the mode configuration.
-The following table describes how the mode argument works.
+The effect of parameter mode is shown in the table below:
-|Mode |First day of week |Range |Week 1 is the first week … |
-|:----|:-----------------|:-------|:-----------------------------|
-|0 |Sunday |1-53 |with a Sunday in this year |
-|1 |Monday |1-53 |with 4 or more days this year |
-|2 |Sunday |1-53 |with a Sunday in this year |
-|3 |Monday |1-53 |with 4 or more days this year |
-|4 |Sunday |1-53 |with 4 or more days this year |
-|5 |Monday |1-53 |with a Monday in this year |
-|6 |Sunday |1-53 |with 4 or more days this year |
-|7 |Monday |1-53 |with a Monday in this year |
+|Mode |First day of week |Week number range |Definition of first week
|
+|:----|:-----------------|:-----------------|:-----------------------------------------------------|
+|0 |Sunday |1-53 |The week containing the first
Sunday of the year |
+|1 |Monday |1-53 |The first week with 4 or more
days in the year |
+|2 |Sunday |1-53 |The week containing the first
Sunday of the year |
+|3 |Monday |1-53 |The first week with 4 or more
days in the year |
+|4 |Sunday |1-53 |The first week with 4 or more
days in the year |
+|5 |Monday |1-53 |The week containing the first
Monday of the year |
+|6 |Sunday |1-53 |The first week with 4 or more
days in the year |
+|7 |Monday |1-53 |The week containing the first
Monday of the year |
-The parameter is Date or Datetime type
+This function behaves consistently with the [yearweek
function](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_yearweek)
in MySQL.
-### example
-```
-mysql> select yearweek('2021-1-1');
-+----------------------+
-| yearweek('2021-1-1') |
-+----------------------+
-| 202052 |
-+----------------------+
-```
-```
-mysql> select yearweek('2020-7-1');
-+----------------------+
-| yearweek('2020-7-1') |
-+----------------------+
-| 202026 |
-+----------------------+
-```
-```
-mysql> select yearweek('2024-12-30',1);
-+------------------------------------+
-| yearweek('2024-12-30 00:00:00', 1) |
-+------------------------------------+
-| 202501 |
-+------------------------------------+
+## Syntax
+
+```sql
+YEARWEEK(`<date_or_time_expr>`[, mode])
```
-### keywords
- YEARWEEK
+## Return Value
+
+Returns an INT type integer in YYYYWW format (first 4 digits are the year,
last 2 digits are the week number), e.g., 202305 represents week 5 of 2023,
202052 represents week 52 of 2020.
+
+- If the week containing the date belongs to the previous year, returns the
previous year's year and week number (e.g., January 1, 2021 might return
202052).
+- If the week containing the date belongs to the next year, returns the next
year's year and week 1 (e.g., December 30, 2024 might return 202501).
+- If input is NULL, returns NULL.
+
+## Examples
+
+```sql
+-- Default mode=0 (Sunday start, first week contains first Sunday)
+-- 2021-01-01 is Friday, the first Sunday of the week is 2020-12-27, so it
belongs to week 52 of 2020
+SELECT YEARWEEK('2021-01-01') AS yearweek_mode0;
++----------------+
+| yearweek_mode0 |
++----------------+
+| 202052 |
++----------------+
+
+-- mode=1 (Monday start, 4-day rule, consistent with WEEKOFYEAR)
+SELECT YEARWEEK('2020-07-01', 1) AS yearweek_mode1;
++----------------+
+| yearweek_mode1 |
++----------------+
+| 202027 |
++----------------+
+
+-- mode=1, cross-year week (2024-12-30 is Monday, the week has ≥4 days in
2025, belongs to week 1 of 2025)
+SELECT YEARWEEK('2024-12-30', 1) AS cross_year_mode1;
++------------------+
+| cross_year_mode1 |
++------------------+
+| 202501 |
++------------------+
+
+-- mode=5 (Monday start, first week contains first Monday)
+-- 2023-01-02 is Monday (first Monday of the year), the week is week 1 of 2023
+SELECT YEARWEEK('2023-01-02', 5) AS yearweek_mode5;
++----------------+
+| yearweek_mode5 |
++----------------+
+| 202301 |
++----------------+
+
+-- Input DATE type
+SELECT YEARWEEK('2023-12-25', 1) AS date_type_mode1;
++------------------+
+| date_type_mode1 |
++------------------+
+| 202352 |
++------------------+
+
+-- Input NULL (returns NULL)
+SELECT YEARWEEK(NULL) AS null_input;
++------------+
+| null_input |
++------------+
+| NULL |
++------------+
+```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year-ceil.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year-ceil.md
index 6bbf1c1b53b..5f597b2fd85 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year-ceil.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year-ceil.md
@@ -5,39 +5,127 @@
}
---
-## year_ceil
## 描述
-## 语法
+YEAR_CEIL
函数用于将输入的日期时间值向上舍入到最接近的指定年间隔的起始时间，间隔单位为年。若指定了起始参考点（origin），则以该点为基准计算间隔；否则默认以
0000-01-01 00:00:00 为参考点。
+
+日期计算公式
+$$
+\text{YEAR\_CEIL}(\langle\text{date\_or\_time\_expr}\rangle,
\langle\text{period}\rangle, \langle\text{origin}\rangle) =
\min\{\langle\text{origin}\rangle + k \times \langle\text{period}\rangle \times
\text{year} \mid k \in \mathbb{Z} \land \langle\text{origin}\rangle + k \times
\langle\text{period}\rangle \times \text{year} \geq
\langle\text{date\_or\_time\_expr}\rangle\}
+$$
+K 代表基准时间到达目标时间所需的周期数
+
+## 语法
```sql
-DATETIME YEAR_CEIL(DATETIME datetime)
-DATETIME YEAR_CEIL(DATETIME datetime, DATETIME origin)
-DATETIME YEAR_CEIL(DATETIME datetime, INT period)
-DATETIME YEAR_CEIL(DATETIME datetime, INT period, DATETIME origin)
+YEAR_CEIL(<date_or_time_expr>)
+YEAR_CEIL(<date_or_time_expr>, origin)
+YEAR_CEIL(<date_or_time_expr>, <period>)
+YEAR_CEIL(<date_or_time_expr>, <period>, <origin>)
```
-将日期转化为指定的时间间隔周期的最近上取整时刻。
+## 参数
+
+| 参数 | 说明
|
+|---------------------|----------------------------------------------------------|
+| `<date_or_time_expr>` | 要向上舍入的日期时间值，支持输入 date/datetime 类型,具体 datetime
和 date 格式请查看 [datetime
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
和 [date
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
+| `<period>` | 可选，表示每个周期由多少秒组成，支持正整数类型（INT）。默认为 1 秒。
|
+| `<origin_datetime>` | 间隔的起始点，支持输入 date/datetime 类型；默认为 0000-01-01 00:00:00。 |
-- datetime：参数是合法的日期表达式。
-- period：参数是指定每个周期有几年组成。
-- origin：开始的时间起点，如果不填，默认是 0001-01-01T00:00:00。
+
+## 返回值
+
+返回与输入类型一致的结果（DATETIME 或 DATE），表示向上舍入后的年间隔起始时间：
+
+- 若输入为 DATE 类型，返回 DATE 类型（仅包含日期部分）；若输入为 DATETIME 或符合格式的字符串，返回 DATETIME
类型（时间部分与 origin 一致，无 origin 时默认为 00:00:00）。
+- 若 `<period>` 为非正数（≤0），函数返回错误。
+- 若任一参数为 NULL，返回 NULL。
+- 若 `<date_or_time_expr>` 恰好是某间隔的起始点（基于 `<period>` 和 `<origin>`），则返回该起始点。
+- 若计算结果超过最大日期时间 9999-12-31 23:59:59，返回错误
+- 若 `<origin>` 日期时间在 `<period>` 之后，也会按照上述公式计算，不过周期 k 为负数。。
+举例
+- 若 `date_or_time_expr` 带有 scale,则返回结果也带有 scale 且小数部分为零
## 举例
-```
-mysql> select year_ceil("2023-07-13 22:28:18", 5);
-+------------------------------------------------------------+
-| year_ceil(cast('2023-07-13 22:28:18' as DATETIMEV2(0)), 5) |
-+------------------------------------------------------------+
-| 2025-01-01 00:00:00 |
-+------------------------------------------------------------+
-1 row in set (0.02 sec)
-```
+```sql
+-- 默认1年间隔（起始点为每年1月1日），2023-07-13向上舍入到2024-01-01
+SELECT YEAR_CEIL('2023-07-13 22:28:18') AS result;
++---------------------+
+| result |
++---------------------+
+| 2024-01-01 00:00:00 |
++---------------------+
+
+-- 指定5年间隔，2023-07-13向上舍入到最近的5年间隔起点（以默认origin计算）
+SELECT YEAR_CEIL('2023-07-13 22:28:18', 5) AS result;
++---------------------+
+| result |
++---------------------+
+| 2025-01-01 00:00:00 |
++---------------------+
+
+---带有 scale 的部分
+mysql> SELECT YEAR_CEIL('2023-07-13 22:28:18.123', 5) AS result;
++-------------------------+
+| result |
++-------------------------+
+| 2026-01-01 00:00:00.000 |
++-------------------------+
+
+-- 输入为DATE类型，返回DATE类型的间隔起点
+SELECT YEAR_CEIL(cast('2023-07-13' as date)) AS result;
++------------+
+| result |
++------------+
+| 2024-01-01 |
++------------+
+
+-- 指定起始基准点origin='2020-01-01'，1年间隔，2023-07-13舍入到2024-01-01
+SELECT YEAR_CEIL('2023-07-13', 1, '2020-01-01') AS result;
++---------------------+
+| result |
++---------------------+
+| 2024-01-01 00:00:00 |
++---------------------+
-### keywords
+-- 指定origin包含时间部分，返回结果的时间部分与origin一致
+SELECT YEAR_CEIL('2023-07-13', 1, '2020-01-01 08:30:00') AS result;
++---------------------+
+| result |
++---------------------+
+| 2024-01-01 08:30:00 |
++---------------------+
- YEAR_CEIL, YEAR, CEIL
+-- 输入恰好是间隔起点（origin='2023-01-01'，period=1），返回自身
+SELECT YEAR_CEIL('2023-01-01', 1, '2023-01-01') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 00:00:00 |
++---------------------+
-### Best Practice
+--- 若 <origin> 日期时间在 <period> 之后，也会按照上述公式计算，不过周期 k 为负数。
+SELECT YEAR_CEIL('2023-07-13 22:22:56', 1, '2028-01-01 08:30:00') AS result;
++---------------------+
+| result |
++---------------------+
+| 2024-01-01 08:30:00 |
++---------------------+
+
+-- 无效period（非正数）
+SELECT YEAR_CEIL('2023-07-13', 0) AS result;
+ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation
year_ceil of 2023-07-13 00:00:00, 0 out of range
+
+-- 任一参数为NULL，返回NULL
+SELECT YEAR_CEIL(NULL, 1) AS result;
++--------+
+| result |
++--------+
+| NULL |
++--------+
+
+-- 计算结果超过最大日期时间，返回错误
+SELECT YEAR_CEIL('9999-12-31 22:28:18', 5) AS result;
+ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation
year_ceil of 9999-12-31 22:28:18, 5 out of range
+```
-还可参阅 [date_ceil](./date-ceil)
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year-floor.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year-floor.md
index 29f072343eb..e1141be39ef 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year-floor.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year-floor.md
@@ -6,90 +6,149 @@
---
## 描述
-用于将给定的日期向下取整到指定的年份间隔起点。它支持多个变体，可按不同方式 指定起始时间 (origin) 和周期 (period) 进行取整。
+YEAR_FLOOR
函数用于将输入的日期时间值向下舍入到最接近的指定年间隔的起始时间，间隔单位为年。若指定了起始参考点（origin），则以该点为基准计算间隔；否则默认以
0000-01-01 00:00:00 为参考点。
+
+计算日期时间公式
+$$
+\text{YEAR\_FLOOR}(\langle\text{date\_or\_time\_expr}\rangle,
\langle\text{period}\rangle, \langle\text{origin}\rangle) =
\max\{\langle\text{origin}\rangle + k \times \langle\text{period}\rangle \times
\text{year} \mid k \in \mathbb{Z} \land \langle\text{origin}\rangle + k \times
\langle\text{period}\rangle \times \text{year} \leq
\langle\text{date\_or\_time\_expr}\rangle\}
+$$
+K 代表的是基准时间到目标时间的周期数
## 语法
```sql
-YEAR_FLOOR(<date_value>, [<period> | <origin_date_value>])
-YEAR_FLOOR(<date_value>, <period>, <origin_date_value>)
+YEAR_FLOOR(<date_or_time_expr>)
+YEAR_FLOOR(<date_or_time_expr>, origin)
+YEAR_FLOOR(<date_or_time_expr>, <period>)
+YEAR_FLOOR(<date_or_time_expr>, <period>, <origin>)
```
## 参数
-| **参数** | **类型** | **说明**
|
-|----------------------|--------------------|--------------------------------------------------------------------|
-| `<date_value>` | `DATE`, `DATETIME` | 需要取整的 `DATE` 或 `DATETIME` 输入值。
|
-| `<origin_date_value>` | `DATE`, `DATETIME` | 用作基准的 `DATE` 或 `DATETIME`
输入值，如果不填，默认值为 `0001-01-01T00:00:00`。 |
-| `<period>` | `INT` | 取整的时间间隔，正整数，表示以多少年为周期进行取整。
|
+| 参数 | 说明
|
+|---------------------|----------------------------------------------------------|
+| `<date_or_time_expr>` | 要向下舍入的日期时间值，支持输入 date/datetime 类型,具体 datetime
和 date 格式请查看 [datetime
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
和 [date
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
+| `<period>` | 可选，表示每个周期由多少秒组成，支持正整数类型（INT）。默认为 1 秒。
|
+| `<origin_datetime>` | 间隔的起始点，支持输入 date/datetime 类型；默认为 0000-01-01 00:00:00。 |
+
+## 返回值
+返回与输入类型一致的结果（DATETIME 或 DATE），表示向下舍入后的年间隔起始时间：
+
+- 若输入为 DATE 类型，返回 DATE 类型（仅包含日期部分）；若输入为 DATETIME 或符合格式的字符串，返回 DATETIME
类型（时间部分与 origin 一致，无 origin 时默认为 00:00:00）。
+- 若 `<period>` 为非正数（≤0），函数返回错误。
+- 若任一参数为 NULL，返回 NULL。
+- 若 `<date_or_time_expr>` 恰好是某间隔的起始点（基于 `<period>` 和 `<origin>`），则返回该起始点。
+- 若计算结果超过最大日期时间 9999-12-31 23:59:59，返回错误。
+- 若 `<origin>` 日期时间在 `<period>` 之后，也会按照上述公式计算，不过周期 k 为负数。
+- 若 `date_or_time_expr` 带有 scale,则返回结果也带有 scale 且小数部分为零
## 举例
-1. 按整年取整
- ```sql
- 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 |
- +----------------------------------------------------------+
- ```
- ```sql
- SELECT YEAR_FLOOR('2023-07-13');
- ```
- ```
- +-------------------------------------------------+
- | year_floor(cast('2023-07-13' as DATETIMEV2(0))) |
- +-------------------------------------------------+
- | 2023-01-01 00:00:00 |
- +-------------------------------------------------+
- ```
-
-2. 以 origin 为基准取整
- ```sql
- 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 为单位取整
- ```sql
- 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 取整
- ```sql
- 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
|
-
+--------------------------------------------------------------------------------------------------+
- ```
- ```sql
- 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
|
-
+-----------------------------------------------------------------------------------------+
- ```
+```sql
+-- 默认1年间隔（起始点为每年1月1日），2023-07-13向下舍入到2023-01-01
+SELECT YEAR_FLOOR('2023-07-13 22:28:18') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 00:00:00 |
++---------------------+
+
+-- 指定5年间隔，2023-07-13向下舍入到最近的5年间隔起点（以默认origin计算）
+SELECT YEAR_FLOOR('2023-07-13 22:28:18', 5) AS result;
++---------------------+
+| result |
++---------------------+
+| 2020-01-01 00:00:00 |
++---------------------+
+
+--带有 scale 的情况
+mysql> SELECT YEAR_FLOOR('2023-07-13 22:28:18.123', 5) AS result;
++-------------------------+
+| result |
++-------------------------+
+| 2021-01-01 00:00:00.000 |
++-------------------------+
+
+-- 输入为DATE类型，返回DATE类型的间隔起点
+SELECT YEAR_FLOOR(cast('2023-07-13' as date)) AS result;
++------------+
+| result |
++------------+
+| 2023-01-01 |
++------------+
+
+-- 指定起始基准点origin='2020-01-01'，1年间隔，2023-07-13舍入到2023-01-01
+SELECT YEAR_FLOOR('2023-07-13', 1, '2020-01-01') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 00:00:00 |
++---------------------+
+
+-- 指定origin包含时间部分，返回结果的时间部分与origin一致
+SELECT YEAR_FLOOR('2023-07-13', 1, '2020-01-01 08:30:00') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 08:30:00 |
++---------------------+
+
+--- 若 <origin> 日期时间在 <period> 之后，也会按照上述公式计算，不过周期 k 为负数。
+SELECT YEAR_FLOOR('2023-07-13 22:22:56', 1, '2028-01-01 08:30:00') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 08:30:00 |
++---------------------+
+
+-- 输入恰好是间隔起点（origin='2023-01-01'，period=1），返回自身
+SELECT YEAR_FLOOR('2023-01-01', 1, '2023-01-01') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 00:00:00 |
++---------------------+
+
+-- 输入时间早于origin起始时间，向下舍入到更早的间隔点
+SELECT YEAR_FLOOR('2019-07-13', 1, '2020-01-01') AS result;
++---------------------+
+| result |
++---------------------+
+| 2019-01-01 00:00:00 |
++---------------------+
+
+-- 跨多个周期的向下舍入，period=3，origin='2020-01-01'
+SELECT YEAR_FLOOR('2025-07-13', 3, '2020-01-01') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 00:00:00 |
++---------------------+
+
+-- 输入时间部分早于origin时间部分，同年内向下舍入
+SELECT YEAR_FLOOR('2023-07-13 06:00:00', 1, '2020-01-01 08:30:00') AS result;
++---------------------+
+| result |
++---------------------+
+| 2022-01-01 08:30:00 |
++---------------------+
+
+-- 输入时间部分晚于origin时间部分，正常向下舍入
+SELECT YEAR_FLOOR('2023-07-13 10:00:00', 1, '2020-01-01 08:30:00') AS result;
++---------------------+
+| result |
++---------------------+
+| 2023-01-01 08:30:00 |
++---------------------+
+-- 无效period（非正数）
+SELECT YEAR_FLOOR('2023-07-13', 0) AS result;
+ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation
year_floor of 2023-07-13 00:00:00, 0 out of range
+-- 任一参数为NULL，返回NULL
+SELECT YEAR_FLOOR(NULL, 1) AS result;
++--------+
+| result |
++--------+
+| NULL |
++--------+
+```
\ No newline at end of file
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year-of-week.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year-of-week.md
index bcc2394b719..78b31da3455 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year-of-week.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year-of-week.md
@@ -5,47 +5,102 @@
}
---
-## year_of_week
-
-year_of_week
-
## 描述
-求 `ISO week date` 标准的周年，与年的区别可以参见
[ISO周日历](https://zh.wikipedia.org/wiki/ISO%E9%80%B1%E6%97%A5%E6%9B%86) 。
+YEAR_OF_WEEK 函数用于返回指定日期在 ISO 8601 周日历标准下的周年（year of week）。与普通年份不同，ISO
周年以周为单位计算，一年的第一周是包含 1 月 4 日的那一周，且该周必须包含至少 4 天属于当年。
+与 [year 函数](./year) 不同，year 函数只是返回输入日期的年份
+
+更多详细信息请参见
[ISO周日历](https://zh.wikipedia.org/wiki/ISO%E9%80%B1%E6%97%A5%E6%9B%86)。
## 别名
-- yow
+- `YOW`
## 语法
-
```sql
-SMALLINT year_of_week(DATE value)
+YEAR_OF_WEEK(`<date_or_time_expr>`)
+YOW(`<date_or_time_expr>`)
```
-
## 参数
-| 参数 | 说明 |
-| -- | -- |
-| `<value>` | 需要求周年的日期 |
+| 参数 | 描述 |
+|------|------|
+| `<date_or_time_expr>` | 输入的日期时间值，支持输入 date/datetime 类型，具体 datetime 和 date
格式请查看 [datetime
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
和 [date
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
## 返回值
-返回 `ISO week date` 标准的周年。
+返回 SMALLINT 类型，表示根据 ISO 8601 周日历标准计算的周年。
+
+- 返回值范围为 1-9999
+- 若输入为 NULL，返回 NULL
+- 若输入为 DATETIME 类型，仅考虑日期部分，忽略时间部分
## 举例
+```sql
+-- 2005-01-01是星期六，该周从2004-12-27开始，包含2004年的天数较多，属于2004年
+SELECT YEAR_OF_WEEK('2005-01-01') AS yow_result;
++------------+
+| yow_result |
++------------+
+| 2004 |
++------------+
+
+-- 使用别名YOW，结果相同
+SELECT YOW('2005-01-01') AS yow_alias_result;
++------------------+
+| yow_alias_result |
++------------------+
+| 2004 |
++------------------+
+
+-- 2005-01-03是星期一，这一周(2005-01-03至2005-01-09)是2005年第一周
+SELECT YEAR_OF_WEEK('2005-01-03') AS yow_result;
++------------+
+| yow_result |
++------------+
+| 2005 |
++------------+
+
+-- 2023-01-01是星期日，该周从2022-12-26开始，属于2022年最后一周
+SELECT YEAR_OF_WEEK('2023-01-01') AS yow_result;
++------------+
+| yow_result |
++------------+
+| 2022 |
++------------+
+
+-- 2023-01-02是星期一，这一周(2023-01-02至2023-01-08)是2023年第一周
+SELECT YEAR_OF_WEEK('2023-01-02') AS yow_result;
++------------+
+| yow_result |
++------------+
+| 2023 |
++------------+
+
+-- DATETIME类型输入，忽略时间部分
+SELECT YEAR_OF_WEEK('2005-01-01 15:30:45') AS yow_datetime;
++--------------+
+| yow_datetime |
++--------------+
+| 2004 |
++--------------+
+
+-- 跨年边界情况：2024-12-30是星期一，属于2025年第一周
+SELECT YEAR_OF_WEEK('2024-12-30') AS yow_result;
++------------+
+| yow_result |
++------------+
+| 2025 |
++------------+
+
+-- 输入为NULL，返回NULL
+SELECT YEAR_OF_WEEK(NULL) AS yow_null;
++----------+
+| yow_null |
++----------+
+| NULL |
++----------+
```
-mysql> select year_of_week('2005-01-01');
-+-----------------------------+
-| year_of_week('2005-01-01') |
-+-----------------------------+
-| 2004 |
-+-----------------------------+
-```
-
-### keywords
-
- YEAR_OF_WEEK
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year.md
index f82e80a058c..c5037f6f670 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/year.md
@@ -5,28 +5,54 @@
}
---
-## year
## 描述
+YEAR 函数用于提取指定日期或时间值中的年份部分，返回整数形式的年份。支持处理 DATE、DATETIME 类型
+
+该函数与 mysql 中的 [year
函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_year)
行为一致
+
## 语法
+```sql
+YEAR(<date_or_time_expr>)
+```
-`INT YEAR(DATETIME date)`
+## 参数
+| 参数 | 说明
|
+|---------------------|----------------------------------------------------------|
+| `<date_or_time_expr>` | 要向上舍入的日期时间值，支持输入 date/datetime 类型,具体 datetime
和 date 格式请查看 [datetime
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
和 [date
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
-返回 date 类型的 year 部分，范围从 1000-9999
+## 返回值
-参数为 Date 或者 Datetime 类型
+返回 date/datetime 类型的 year 部分，INT 类型，范围从 0-9999
+
+- 若输入的参数为 NULL，返回 NULL
## 举例
+```sql
+-- 提取DATE类型的年份
+SELECT YEAR('1987-01-01') AS year_date;
++-----------+
+| year_date |
++-----------+
+| 1987 |
++-----------+
+
+-- 提取DATETIME类型的年份（忽略时分秒）
+SELECT YEAR('2024-05-20 14:30:25') AS year_datetime;
++---------------+
+| year_datetime |
++---------------+
+| 2024 |
++---------------+
+
+
+-- 输入为NULL（返回NULL）
+SELECT YEAR(NULL) AS null_input;
++------------+
+| null_input |
++------------+
+| NULL |
++------------+
```
-mysql> select year('1987-01-01');
-+-----------------------------+
-| year('1987-01-01 00:00:00') |
-+-----------------------------+
-| 1987 |
-+-----------------------------+
-```
-
-### keywords
- YEAR
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/years-add.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/years-add.md
index 8fa7fd3e50f..205b1bddd11 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/years-add.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/years-add.md
@@ -7,35 +7,93 @@
## 描述
-返回一个新的日期时间值，该值是在输入的日期时间上增加指定的年数。
+YEARS_ADD 函数用于在指定的日期或时间值上增加（或减少）指定数量的年数，返回调整后的日期或时间。支持处理 DATE、DATETIME
类型，年数可为正数（增加）或负数（减少）。
+
+该函数与 [date_add 函数](./date-add) 和 mysql 中的 [date_add
函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-add)
使用 YEAR 为单位的行为一致
## 语法
```sql
-YEARS_ADD(<date>, <years>)
+YEARS_ADD(<date_or_time_expr>, <years>)
```
## 参数
| 参数 | 说明 |
| ---- | ---- |
-| `<date>` | 输入的日期时间值，类型为 DATETIME 或 DATE |
-| `<years>` | 要增加的年数，类型为 INT |
+| `<date_or_time_expr>` | 输入的日期时间值，支持输入 date/datetime 类型,具体 datetime 和 date
格式请查看 [datetime
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
和 [date
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/date-conversion)|
+| `<years>` | 要增加的年数，类型为 INT,负数表示减少，正数表示增加 |
+
## 返回值
-返回与输入 `<date>` 类型相同的值（DATETIME 或 DATE），表示在输入日期时间的基础上增加指定年数后的时间值。
+返回与输入类型一致的结果（DATE 或 DATETIME），表示调整后的日期或时间：
+
+- 若输入为 DATE 类型，返回值仍为 DATE 类型（仅调整年月日）。
+- 若输入为 DATETIME 类型，返回值仍为 DATETIME 类型（年月日调整后，时分秒保持不变）。
+- <years_value> 为负数时表示减少年数（等价于 YEARS_SUB(<datetime_or_date_value>,
<years_value>)）。
+- 任意输入参数为 NULL，返回 NULL。
+- 若计算结果超出日期类型的有效范围（0000-01-01 00:00:00 至 9999-12-31 23:59:59），返回错误。
+- 若调整后月份的天数不足（如 2 月 29 日加 1 年且次年非闰年），自动调整为当月最后一天（如 2020-02-29 加 1 年返回
2021-02-28）。
+举例
## 举例
```sql
-SELECT YEARS_ADD('2020-01-31 02:02:02', 1);
-```
+-- DATETIME类型增加1年（基础功能，时分秒保持不变）
+SELECT YEARS_ADD('2020-01-31 02:02:02', 1) AS add_1_year_datetime;
++-----------------------+
+| add_1_year_datetime |
++-----------------------+
+| 2021-01-31 02:02:02 |
++-----------------------+
+
+-- DATETIME类型减少1年（负数years_value，跨年度）
+SELECT YEARS_ADD('2023-05-10 15:40:20', -1) AS subtract_1_year_datetime;
++--------------------------+
+| subtract_1_year_datetime |
++--------------------------+
+| 2022-05-10 15:40:20 |
++--------------------------+
+
+-- DATE类型增加3年（仅调整日期）
+SELECT YEARS_ADD('2019-12-25', 3) AS add_3_year_date;
++------------------+
+| add_3_year_date |
++------------------+
+| 2022-12-25 |
++------------------+
+
+-- 闰日处理（2020-02-29加1年，次年为平年）
+SELECT YEARS_ADD('2020-02-29', 1) AS leap_day_adjust;
++------------------+
+| leap_day_adjust |
++------------------+
+| 2021-02-28 |
++------------------+
+
+-- 跨月天数调整（1月31日加1年到2月）
+SELECT YEARS_ADD('2023-01-31', 1) AS month_day_adjust;
++------------------+
+| month_day_adjust |
++------------------+
+| 2024-01-31 |
++------------------+
+
+
+-- 输入为NULL（返回NULL）
+SELECT YEARS_ADD(NULL, 5) AS null_input;
++------------+
+| null_input |
++------------+
+| NULL |
++------------+
+
+-- 计算结果超出日期时间范围（上限）
+SELECT YEARS_ADD('9999-12-31', 1);
+ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation
years_add of 9999-12-31, 1 out of range
-```text
-+-------------------------------------+
-| years_add('2020-01-31 02:02:02', 1) |
-+-------------------------------------+
-| 2021-01-31 02:02:02 |
-+-------------------------------------+
+-- 计算结果超出日期时间范围（下限）
+SELECT YEARS_ADD('0000-01-01', -1);
+ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation
years_add of 0000-01-01, -1 out of range
```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/years-diff.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/years-diff.md
index 720ad0643a6..c387f3c8ac9 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/years-diff.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/years-diff.md
@@ -6,36 +6,90 @@
---
## 描述
-
-计算两个日期时间值之间相差的年数。
+YEARS_DIFF 函数用于计算两个日期或时间值之间的完整年数差值，结果为结束时间减去开始时间的年数。支持处理 DATE、DATETIME
类型，计算时会考虑完整的时间差（包括月份、日期及时分秒）。
## 语法
```sql
-YEARS_DIFF(<enddate>, <startdate>)
+YEARS_DIFF(<date_or_time_expr1>, <date_or_time_expr2>)
```
## 参数
| 参数 | 说明 |
| ---- | ---- |
-| `<enddate>` | 结束日期，类型为 DATETIME 或 DATE |
-| `<startdate>` | 开始日期，类型为 DATETIME 或 DATE |
+| `<date_or_time_expr1>` | 结束日期，支持输入 date/datetime 类型,具体 datetime 和 date 格式请查看
[datetime
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
和 [date
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/date-conversion)
|
+| `<date_or_time_expr2>` | 开始日期，支持输入 date/datetime 类型和符合日期时间格式的字符串 |
## 返回值
-返回类型为 INT，表示两个日期之间相差的年数。
+返回 INT 类型的整数，表示 <date_or_time_expr1> 与 <date_or_time_expr2> 之间的完整年数差值：
+
+- 若 <date_or_time_expr1> 晚于 <date_or_time_expr2>，返回正数（需满足 “满一年”
条件，如'2022-03-15 08:30:00', '2021-03-15 09:10:00' 实际相差日期时间不满一年，返回 0）。
+- 若<date_or_time_expr1> 早于 <date_or_time_expr2>，返回负数（计算方式同上，结果取负）。
+- 若输入为 DATE 类型，默认其时间部分为 00:00:00.
+- 若任一参数为 NULL，返回 NULL。
+- 闰年2月特殊情况（如2024是闰年，2月29日 vs 2023年2月28日，满一年）
## 举例
```sql
-SELECT YEARS_DIFF('2020-12-25', '2019-10-25');
-```
+--- 年份差1年，且月-日相等（满一年）
+SELECT YEARS_DIFF('2020-12-25', '2019-12-25') AS diff_full_year;
++----------------+
+| diff_full_year |
++----------------+
+| 1 |
++----------------+
+
+-- 年份差1年，但结束月-日早于开始月-日（不足一年）
+SELECT YEARS_DIFF('2020-11-25', '2019-12-25') AS diff_less_than_year;
++---------------------+
+| diff_less_than_year |
++---------------------+
+| 0 |
++---------------------+
+
+-- 包含时间部分的DATETIME类型（
+SELECT YEARS_DIFF('2022-03-15 08:30:00', '2021-03-15 09:10:00') AS
diff_datetime;
++---------------+
+| diff_datetime |
++---------------+
+| 0 |
++---------------+
+
+-- DATE与DATETIME混合计算，输入 date 类型会把时间部分默认设置为 00:00:00
+SELECT YEARS_DIFF('2024-05-20', '2020-05-20 12:00:00') AS diff_mixed;
++-----------+
+| diff_mixed |
++-----------+
+| 3 |
++-----------+
+
+-- 结束时间早于开始时间，返回负数
+SELECT YEARS_DIFF('2018-06-10', '2020-06-10') AS diff_negative;
++---------------+
+| diff_negative |
++---------------+
+| -2 |
++---------------+
+
+-- 闰年2月特殊情况（2024是闰年，2月29日 vs 2023年2月28日，满一年）
+SELECT YEARS_DIFF('2024-02-29', '2023-02-28') AS leap_year_diff;
++----------------+
+| leap_year_diff |
++----------------+
+| 1 |
++----------------+
+
-```text
-+----------------------------------------------------------+
-| years_diff('2020-12-25 00:00:00', '2019-10-25 00:00:00') |
-+----------------------------------------------------------+
-| 1 |
-+----------------------------------------------------------+
+-- 任一参数为NULL（返回NULL）
+SELECT
+ YEARS_DIFF(NULL, '2023-03-15') AS null_input1,
+ YEARS_DIFF('2023-03-15', NULL) AS null_input2;
++------------+------------+
+| null_input1 | null_input2 |
++------------+------------+
+| NULL | NULL |
++------------+------------+
```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/years-sub.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/years-sub.md
index e52284b2099..02b2f9da75d 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/years-sub.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/years-sub.md
@@ -7,35 +7,80 @@
## 描述
-返回一个新的日期时间值，该值是从输入的日期时间中减去指定的年数。
+YEARS_SUB 函数用于在指定的日期或时间值上减少（或增加）指定数量的年数，返回调整后的日期或时间（本质是减去 years_value × 1
年）。支持处理 DATE、DATETIME 类型，年数可为正数（减少）或负数（增加）。
+
+该函数与 [date_sub 函数](./date-sub) 和 mysql 中的 [date_sub
函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-sub)
使用 YEAR 为单位的行为一致
## 语法
```sql
-YEARS_SUB(<date>, <years>)
+YEARS_SUB(<date_or_time_expr>, <years>)
```
## 参数
| 参数 | 说明 |
| ---- | ---- |
-| `<date>` | 输入的日期时间值，类型为 DATETIME 或 DATE |
-| `<years>` | 要减去的年数，类型为 INT |
+| `<date_or_time_expr>` | 输入的日期时间值，支持输入 date/datetime 类型,具体 datetime 和 date
格式请查看 [datetime
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/datetime-conversion)
和 [date
的转换](../../../../../current/sql-manual/basic-element/sql-data-types/conversion/date-conversion)|
+| `<years>` | 要减少的年数，类型为 INT,整数表示减少，负数表示增加 |
## 返回值
-返回与输入 `<date>` 类型相同的值（DATETIME 或 DATE），表示从输入日期时间中减去指定年数后的时间值。
+返回与输入类型一致的结果（DATE 或 DATETIME），表示调整后的日期或时间：
+- 若输入为 DATE 类型，返回值仍为 DATE 类型（仅调整年月日）。
+- 若输入为 DATETIME 类型，返回值仍为 DATETIME 类型（年月日调整后，时分秒保持不变）。
+- <years_value> 为负数时表示增加年数（等价于 YEARS_ADD(<datetime_or_date_value>,
<years_value>)）。
+- 任意输入参数为 NULL，返回 NULL。
+- 若计算结果超出日期类型的有效范围（0000-01-01 00:00:00 至 9999-12-31 23:59:59），返回错误。
+- 若调整后月份的天数不足（如从润年 2 月 29 日减少 1 年到平年 2 月 28 日），自动调整为当月实际天数。
## 举例
```sql
-SELECT YEARS_SUB('2020-02-02 02:02:02', 1);
-```
+-- DATETIME类型减少1年（基础功能，时分秒保持不变）
+SELECT YEARS_SUB('2020-02-02 02:02:02', 1) AS sub_1_year_datetime;
++-----------------------+
+| sub_1_year_datetime |
++-----------------------+
+| 2019-02-02 02:02:02 |
++-----------------------+
-```text
-+-------------------------------------+
-| years_sub('2020-02-02 02:02:02', 1) |
-+-------------------------------------+
-| 2019-02-02 02:02:02 |
-+-------------------------------------+
-```
+--DATETIME类型增加1年（负数years_value，跨年度）
+SELECT YEARS_SUB('2022-05-10 15:40:20', -1) AS add_1_year_datetime;
++-----------------------+
+| add_1_year_datetime |
++-----------------------+
+| 2023-05-10 15:40:20 |
++-----------------------+
+
+-- DATE类型减少3年（仅调整日期）
+SELECT YEARS_SUB('2022-12-25', 3) AS sub_3_year_date;
++------------------+
+| sub_3_year_date |
++------------------+
+| 2019-12-25 |
++------------------+
+
+-- 闰日处理（从闰年 2 月 29 日减1年到平年 2 月 28 日）
+SELECT YEARS_SUB('2020-02-29', 1) AS leap_day_adjust_1;
++-------------------+
+| leap_day_adjust_1 |
++-------------------+
+| 2019-02-28 |
++-------------------+
+
+-- 输入为NULL（返回NULL）
+SELECT YEARS_SUB(NULL, 5) AS null_input;
++------------+
+| null_input |
++------------+
+| NULL |
++------------+
+
+-- 计算结果超出日期时间范围（上限）
+SELECT YEARS_SUB('9999-12-31', -1);
+ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation
year_add of 9999-12-31, 1 out of range
+
+--计算结果超出日期时间范围（下限）
+SELECT YEARS_SUB('0000-01-01', 1);
+ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation
year_add of 0000-01-01, -1 out of range
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/yearweek.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/yearweek.md
index 715b01ca1d8..9ea564fb1e9 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/yearweek.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/yearweek.md
@@ -5,15 +5,12 @@
}
---
-## yearweek
## 描述
-## 语法
-`INT YEARWEEK(DATE date[, INT mode])`
+YEARWEEK 函数用于返回指定日期对应的 “年份 + 周数” 组合（格式为 YYYYWW，如 202301 表示 2023 年第 1
周）。该函数通过可选参数 mode 灵活定义一周的起始日和 “第一周” 的判断标准，默认使用 mode=0。
+
+周数范围为 1-53，具体取决于 mode 的配置。
-返回指定日期的年份和星期数。mode 的值默认为 0。
-当日期所在的星期属于上一年时，返回的是上一年的年份和星期数；
-当日期所在的星期属于下一年时，返回的是下一年的年份，星期数为 1。
参数 mode 的作用参见下面的表格：
|Mode |星期的第一天 |星期数的范围 |第一个星期的定义 |
@@ -27,35 +24,71 @@
|6 |星期日 |1-53 |这一年的日期所占的天数大于等于 4 天的第一个星期|
|7 |星期一 |1-53 |这一年中的第一个星期一所在的星期 |
-参数为 Date 或者 Datetime 类型
+该函数与 mysql 中的 [yearweek
函数](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_yearweek)
行为一致
-## 举例
+## 语法
+```sql
+YEARWEEK(<date_or_time_expr>[, mode])
```
-mysql> select yearweek('2021-1-1');
-+----------------------+
-| yearweek('2021-1-1') |
-+----------------------+
-| 202052 |
-+----------------------+
-```
-```
-mysql> select yearweek('2020-7-1');
-+----------------------+
-| yearweek('2020-7-1') |
-+----------------------+
-| 202026 |
-+----------------------+
-```
-```
-mysql> select yearweek('2024-12-30',1);
-+------------------------------------+
-| yearweek('2024-12-30 00:00:00', 1) |
-+------------------------------------+
-| 202501 |
-+------------------------------------+
-```
-### keywords
+## 返回值
+返回 INT 类型的整数，格式为 YYYYWW（前 4 位为年份，后 2 位为周数），例如 202305 表示 2023 年第 5 周，202052 表示
2020 年第 52 周。
+
+- 若日期所在周属于上一年，返回上一年的年份和周数（如 2021 年 1 月 1 日可能返回 202052）。
+- 若日期所在周属于下一年，返回下一年的年份和第 1 周（如 2024 年 12 月 30 日可能返回 202501）。
+- 若输入为NULL，返回 NULL。
+
+## 举例
+
+```sql
+-- 默认mode=0（星期日起始，第一周含第一个星期日）
+-- 2021-01-01是星期五，所在周的第一个星期日为2020-12-27，故属于2020年第52周
+SELECT YEARWEEK('2021-01-01') AS yearweek_mode0;
++----------------+
+| yearweek_mode0 |
++----------------+
+| 202052 |
++----------------+
+
+-- mode=1（星期一起始，4天规则，与WEEKOFYEAR一致）
+SELECT YEARWEEK('2020-07-01', 1) AS yearweek_mode1;
++----------------+
+| yearweek_mode1 |
++----------------+
+| 202027 |
++----------------+
+
+-- mode=1，跨年周（2024-12-30是星期一，所在周2025年日期占比≥4天，属于2025年第1周）
+SELECT YEARWEEK('2024-12-30', 1) AS cross_year_mode1;
++-------------------+
+| cross_year_mode1 |
++-------------------+
+| 202501 |
++-------------------+
+
+-- mode=5（星期一起始，第一周含第一个星期一）
+-- 2023-01-02是星期一（当年第一个星期一），所在周为2023年第1周
+SELECT YEARWEEK('2023-01-02', 5) AS yearweek_mode5;
++----------------+
+| yearweek_mode5 |
++----------------+
+| 202301 |
++----------------+
+
+-- 输入为DATE类型
+SELECT YEARWEEK('2023-12-25', 1) AS date_type_mode1;
++-------------------+
+| date_type_mode1 |
++-------------------+
+| 202352 |
++-------------------+
- YEARWEEK
+-- 输入为NULL（返回NULL）
+SELECT YEARWEEK(null);
++----------------+
+| YEARWEEK(null) |
++----------------+
+| NULL |
++----------------+
+```
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
