This is an automated email from the ASF dual-hosted git repository.
yiguolei 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 bd050fbbfa9 [fix](function) str_to_date and from_iso8601_date return
null instead of raise error (#3043)
bd050fbbfa9 is described below
commit bd050fbbfa9d02d2f92b01dca91c6241c07ec441
Author: zclllyybb <[email protected]>
AuthorDate: Wed Nov 5 00:46:15 2025 +0800
[fix](function) str_to_date and from_iso8601_date return null instead of
raise error (#3043)
docs of https://github.com/apache/doris/pull/57669
## Versions
- [x] dev
- [ ] 3.x
- [ ] 2.1
- [ ] 2.0
## Languages
- [x] Chinese
- [x] English
---
.../date-time-functions/from-iso8601-date.md | 28 +++++++++++-----
.../date-time-functions/str-to-date.md | 26 +++++++++++----
.../string-functions/from-iso8601-date.md | 39 ----------------------
.../date-time-functions/from-iso8601-date.md | 21 ++++++++++--
.../date-time-functions/str-to-date.md | 27 ++++++++++-----
5 files changed, 76 insertions(+), 65 deletions(-)
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/from-iso8601-date.md
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/from-iso8601-date.md
index 2427b9a5dce..58db33ae219 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/from-iso8601-date.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/from-iso8601-date.md
@@ -8,6 +8,7 @@
Converts an ISO8601 formatted date expression to a DATE type date expression.
Date strings conforming to the ISO 8601 standard, supported formats include:
+
- YYYY: Year only (returns January 1st of that year)
- YYYY-MM: Year and month (returns the 1st of that month)
- YYYY-DDD: Year + day of year (DDD range 1-366, e.g., 0000-059 represents the
59th day of year 0000)
@@ -30,9 +31,10 @@ from_iso8601_date(<dt>)
## Return Value
Returns DATE type in the format YYYY-MM-DD, representing the parsed specific
date.
-- If the input format is invalid (e.g., week number exceeds 53), returns NULL.
-- If the input contains time or timezone information (e.g., 2023-10-01T12:34),
returns NULL.
-- Input NULL, returns NULL
+
+- If the input format is invalid, returns NULL;
+- Result is not valid datetime value, returns NULL;
+- Input NULL, returns NULL.
## Examples
@@ -55,7 +57,6 @@ select
from_iso8601_date('2021-059') as day_59,
from_iso8601_date('2021-060') as day_60,
from_iso8601_date('2024-366') as day_366;
-
+------------+------------+------------+------------+
| day_1 | day_59 | day_60 | day_366 |
+------------+------------+------------+------------+
@@ -80,16 +81,27 @@ select from_iso8601_date('0522-W01-4') as week_4;
---YYY-MMM format, Monday of the first week is in year 521
select from_iso8601_date('0522-W01') as week_1;
-
+------------+
| week_1 |
+------------+
| 0521-12-29 |
+------------+
----invalid style, return error
+---invalid style, return NULL
select from_iso8601_date('2023-10-01T12:34:10');
-ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation from_iso8601_date of
2023-10-01T12:34:10 is invalid
++------------------------------------------+
+| from_iso8601_date('2023-10-01T12:34:10') |
++------------------------------------------+
+| NULL |
++------------------------------------------+
+
+---invalid result value, return NULL
+select from_iso8601_date('2024-400');
++-------------------------------+
+| from_iso8601_date('2024-400') |
++-------------------------------+
+| NULL |
++-------------------------------+
---input NULL
select from_iso8601_date(NULL);
@@ -98,4 +110,4 @@ select from_iso8601_date(NULL);
+-------------------------+
| NULL |
+-------------------------+
-```
\ No newline at end of file
+```
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/str-to-date.md
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/str-to-date.md
index c73791639c0..3b51abf61e9 100644
---
a/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/str-to-date.md
+++
b/docs/sql-manual/sql-functions/scalar-functions/date-time-functions/str-to-date.md
@@ -33,9 +33,11 @@ In addition, `<format>` supports the following alternative
formats and interpret
| `yyyy-MM-dd HH:mm:ss` | `%Y-%m-%d %H:%i:%s` |
## Return Value
+
Returns a DATETIME value representing the converted date and time.
Date and time matching method uses two pointers to point to the start of both
strings:
+
1. When the format string encounters a % symbol, the next letter after % is
used to match the corresponding part of the date/time string. If it does not
match (e.g., %Y tries to match a time part like 10:10:10, or % is followed by
an unsupported character like %*), an error is returned. If matched
successfully, move to the next character for parsing.
2. At any time, if either string encounters a space character, skip it and
parse the next character.
3. When matching ordinary letters, check if the characters pointed to by both
pointers are equal. If not, return an error; if equal, parse the next character.
@@ -43,9 +45,11 @@ Date and time matching method uses two pointers to point to
the start of both st
5. When the format string pointer reaches the end, matching ends.
6. Finally, check whether the matched time parts are valid (e.g., month must
be in [1,12]). If invalid, return an error; if valid, return the parsed date
and time.
+Error handling:
+
- If any parameter is NULL, returns NULL;
-- If `<format>` is an empty string, returns an error;
-- If matching fails, returns an error.
+- If `<format>` is an empty string, returns NULL;
+- If matching between `<datetime_str>` and `<format>` fails, returns NULL.
## Examples
@@ -90,9 +94,13 @@ SELECT STR_TO_DATE('Oct 5 2023 3:45:00 PM', '%b %d %Y
%h:%i:%s %p') AS result;
| 2023-10-05 15:45:00 |
+---------------------+
--- Format does not match string (returns error)
+-- Format does not match string (returns NULL)
SELECT STR_TO_DATE('2023/01/01', '%Y-%m-%d') AS result;
-ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation str_to_date of 2023/01/01 is invalid
++--------+
+| result |
++--------+
+| NULL |
++--------+
-- String contains extra characters (automatically ignored)
SELECT STR_TO_DATE('2023-01-01 10:00:00 (GMT)', '%Y-%m-%d %H:%i:%s') AS result;
@@ -118,7 +126,11 @@ SELECT STR_TO_DATE(NULL, '%Y-%m-%d'),
STR_TO_DATE('2023-01-01', NULL) AS result;
| NULL | NULL |
+--------------------------------+--------+
--- Format is an empty string (returns error)
+-- Format is an empty string (returns NULL)
SELECT STR_TO_DATE('2023-01-01', '') AS result;
-ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation str_to_date of 2023-01-01 is invalid
-```
\ No newline at end of file
++--------+
+| result |
++--------+
+| NULL |
++--------+
+```
diff --git
a/docs/sql-manual/sql-functions/scalar-functions/string-functions/from-iso8601-date.md
b/docs/sql-manual/sql-functions/scalar-functions/string-functions/from-iso8601-date.md
deleted file mode 100644
index bc62c476dc4..00000000000
---
a/docs/sql-manual/sql-functions/scalar-functions/string-functions/from-iso8601-date.md
+++ /dev/null
@@ -1,39 +0,0 @@
----
-{
- "title": "FROM_ISO8601_DATE",
- "language": "en"
-}
----
-## Description
-
-Converts an ISO8601 formatted date expression to a DATE type date expression.
-
-## Syntax
-
-```sql
-from_iso8601_date(<dt>)
-```
-
-## Parameters
-
-| Parameter | Description |
-| -- | -- |
-| `<dt>` | An ISO8601 formatted date |
-
-## Return Value
-
-A DATE type date expression.
-
-## Examples
-
-```sql
-SELECT
from_iso8601_date('0000-01'),from_iso8601_date('0000-W01'),from_iso8601_date('0000-059');
-```
-
-```text
-+------------------------------+-------------------------------+-------------------------------+
-| from_iso8601_date('0000-01') | from_iso8601_date('0000-W01') |
from_iso8601_date('0000-059') |
-+------------------------------+-------------------------------+-------------------------------+
-| 0000-01-01 | 0000-01-03 | 0000-02-28
|
-+------------------------------+-------------------------------+-------------------------------+
-```
\ 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/from-iso8601-date.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/from-iso8601-date.md
index 89b769059fa..8467f1d15bb 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/from-iso8601-date.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/from-iso8601-date.md
@@ -8,6 +8,7 @@
将 ISO8601 格式的日期表达式转化为 date 类型的日期表达式。
符合 ISO 8601 标准的日期字符串，支持的格式包括：
+
- YYYY：仅年份（返回该年 1 月 1 日）
- YYYY-MM：年月（返回该月 1 日）
- YYYY-DDD：年 + 年中的天数（DDD 范围 1-366，如 0000-059 表示 0000 年第 59 天）
@@ -30,7 +31,9 @@ from_iso8601_date(<dt>)
## 返回值
返回 DATE 类型，格式为 YYYY-MM-DD，表示解析后的具体日期。
-- 若输入格式无效，返回错误
+
+- 若输入格式错误，返回 NULL
+- 结果不为合法日期时间值，返回 NULL
- 输入 NULL，返回 NULL
## 举例
@@ -82,9 +85,21 @@ select from_iso8601_date('0522-W01') as week_1;
| 0521-12-29 |
+------------+
----格式错误，返回错误
+---格式错误，返回 NULL
select from_iso8601_date('2023-10-01T12:34:10');
-ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation from_iso8601_date of
2023-10-01T12:34:10 is invalid
++------------------------------------------+
+| from_iso8601_date('2023-10-01T12:34:10') |
++------------------------------------------+
+| NULL |
++------------------------------------------+
+
+---值错误，返回 NULL
+select from_iso8601_date('2024-400');
++-------------------------------+
+| from_iso8601_date('2024-400') |
++-------------------------------+
+| NULL |
++-------------------------------+
---输入 NULL
select from_iso8601_date(NULL);
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/str-to-date.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/str-to-date.md
index 0e4099464aa..a5ea1b33090 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/str-to-date.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/scalar-functions/date-time-functions/str-to-date.md
@@ -33,9 +33,11 @@ STR_TO_DATE(<datetime_str>, <format>)
|`yyyy-MM-dd HH:mm:ss`|`%Y-%m-%d %H:%i:%s`|
## 返回值
+
返回一个 DATETIME 类型值，表示转换后的日期时间。
-日期时间匹配方式，用两根指针指向两字符串起始位置
+日期时间匹配方式，用两根指针指向两字符串起始位置，然后：
+
1. 当遇格式字符串到 % 符号时，会根据 % 下一个字母匹配时间字符对应的时间部分，若不匹配（如 %Y 匹配日期时间部分却为 10:10:10 或者 %
不支持解析的字符如 %*），则返回错误，匹配成功则移动到下一个字符解析。
2. 任意时刻两串中的任一个遇到空格字符，直接跳过解析下一个字符
3. 当遇到普通字母的匹配，则查看两字符串现在指针所指向的字符是否相等，不相等则返回错误，相等则解析下一个字符
@@ -43,10 +45,11 @@ STR_TO_DATE(<datetime_str>, <format>)
5. 当格式字符串指向末尾时，匹配结束。
6. 最后检查匹配时间部分是否合法（如月份必须在 [1，12] 区间内），如果不合法，则返回错误，合法则返回解析出的日期时间
+错误处理：
- 若任一参数为 NULL，返回 NULL；
-- 若 `<format>` 为空字符串，返回错误；
-- 匹配失败，返回错误
+- 若 `<format>` 为空字符串，返回 NULL；
+- `<datetime_str>` 与 `<format>` 匹配失败，返回 NULL
## 举例
@@ -91,9 +94,13 @@ SELECT STR_TO_DATE('Oct 5 2023 3:45:00 PM', '%b %d %Y
%h:%i:%s %p') AS result;
| 2023-10-05 15:45:00 |
+---------------------+
--- 格式与字符串不匹配（返回错误）
+-- 格式与字符串不匹配（返回 NULL）
SELECT STR_TO_DATE('2023/01/01', '%Y-%m-%d') AS result;
-ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation str_to_date of 2023/01/01 is invalid
++--------+
+| result |
++--------+
+| NULL |
++--------+
-- 字符串包含多余字符（自动忽略）
SELECT STR_TO_DATE('2023-01-01 10:00:00 (GMT)', '%Y-%m-%d %H:%i:%s') AS result;
@@ -119,7 +126,11 @@ SELECT STR_TO_DATE(NULL, '%Y-%m-%d'),
STR_TO_DATE('2023-01-01', NULL) AS result;
| NULL | NULL |
+--------------------------------+--------+
--- 格式为空字符串（返回错误））
+-- 格式为空字符串（返回 NULL）
SELECT STR_TO_DATE('2023-01-01', '') AS result;
-ERROR 1105 (HY000): errCode = 2, detailMessage =
(10.16.10.3)[INVALID_ARGUMENT]Operation str_to_date of 2023-01-01 is invalid
-```
\ No newline at end of file
++--------+
+| result |
++--------+
+| NULL |
++--------+
+```
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
