提问人：data en 提问时间：9/29/2023 更新时间：9/30/2023 访问量：59
如何使用Big Query将任何日期格式和日期时间格式转换为特定的日期格式？
How to convert any date format and date time format into a particular date format using Big Query?
问：
我有一个具有不同日期格式和日期时间格式的日期列。现在，我想将格式转换为 .我使用了 .但是我写的查询抛出错误。如何在大查询中查询，以便更新语句可以转换任何类型的日期格式和日期时间格式？dd/mm/yyyyPARSE_TIMESTAMP,FORMAT_TIMESTAMP and FORMAT_DATE
我的数据：-
2020/12/23
2020-03-24
20200524
07/30/2020
09-30-2021
09/20/20
12-24-20
20/09/2020
22-09-2023
09-2023
10-20
Jan2020
jan-2020
2020-jun
2020.12.22
12.22.2020
2020/04/22 12:30:09
2023-09-24 11:30:20
2022/09/23 11:20:30.22
2020-12-20 11:24:30.02
20220923141530 (September 23, 2022, 14:15:30)
09/23/2022 14:15:30
09-23-2022 14:15:30
大查询：-
UPDATE `project.dataset.table`
SET Date =
CASE
WHEN PARSE_TIMESTAMP('%Y/%m/%d', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y/%m/%d', Date))
WHEN PARSE_TIMESTAMP('%Y-%m-%d', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y-%m-%d', Date))
WHEN PARSE_DATE('%Y%m%d', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%Y%m%d', Date))
WHEN PARSE_DATE('%m/%d/%Y', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m/%d/%Y', Date))
WHEN PARSE_DATE('%m-%d-%Y', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m-%d-%Y', Date))
WHEN PARSE_DATE('%m/%d/yy', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m/%d/yy', Date))
WHEN PARSE_DATE('%m-%d-yy', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m-%d-yy', Date))
WHEN PARSE_DATE('%d/%m/%Y', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%d/%m/%Y', Date))
WHEN PARSE_DATE('%d-%m-%Y', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%d-%m-%Y', Date))
WHEN PARSE_DATE('%m-%Y', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m-%Y', Date))
WHEN PARSE_DATE('%m-yy', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m-yy', Date))
WHEN PARSE_DATE('%b%Y', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%b%Y', Date))
WHEN PARSE_DATE('%b-%Y', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%b-%Y', Date))
WHEN PARSE_DATE('%Y-%b', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%Y-%b', Date))
WHEN PARSE_DATE('%Y.%m.%d', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%Y.%m.%d', Date))
WHEN PARSE_DATE('%m.%d.%Y', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m.%d.%Y', Date))
WHEN PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S', Date))
WHEN PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', Date))
WHEN PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S.%f', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S.%f', Date))
WHEN PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S.%f', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S.%f', Date))
WHEN PARSE_TIMESTAMP('%Y%m%d%H%M%S', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y%m%d%H%M%S', Date))
WHEN PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', Date) IS NOT NULL THEN
FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', Date))
ELSE NULL
END
WHERE TRUE;
错误：-
Mismatch between format character '/' and string character '-'
答：
0赞
rtenha
9/30/2023
#1
您看到错误是因为您的语句正在计算每一行数据。因此，使用您的示例列表，第一项被第一项计算为真（并且 case 语句的其余部分短路）。但是，第 2 项失败，第一项失败，并出现给定错误。您可以通过以下方式确认这一点：case when2020/12/23case when2020-03-24case when
-- This won't work
select PARSE_TIMESTAMP('%Y/%m/%d', '2020-03-24')
在解析之前，您可能应该考虑在 case 语句中使用正则表达式函数来测试输入格式。
0赞
Syed Sajjad Askari
9/30/2023
#2
如果出现错误“格式字符”/“和字符串字符”-“不匹配，则表示输入日期字符串与预期格式不匹配。您可以尝试通过更改查询中的格式字符串或更正输入数据来解决此问题。
以下是在 BigQuery 中转换日期和日期时间格式时处理错误的一些提示：
使用 TRY（） 函数捕获和处理错误。 使用 SAFE_PARSE_DATE（） 函数分析可能包含错误的日期字符串。 使用 SAFE_CAST（） 函数将日期和日期时间字符串转换为适当的数据类型。 如需详细了解 BigQuery 中的日期和日期时间函数，请参阅参考文档：https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions。
评论