如何在BigQuery中将多个字符串格式转换为一个日期格式

Question

我的数据中有一个日期列，比如"付款日期"，它有多种字符串格式，如ddmmyyyy、ddmyyyy和yyyymmdd。有人知道我如何将所有这些转换为统一的日期格式吗？比如BigQuery中的dd-mm-yyyy？

Answer 1

以下示例适用于BigQuery Standard SQL:

#standardSQL
SELECT payment_date, 
FORMAT_DATE('%d-%m-%Y', CASE LENGTH(payment_date) 
WHEN 7 THEN 
SAFE.DATE(
SAFE_CAST(SUBSTR(payment_date, -4) AS INT64), 
SAFE_CAST(SUBSTR(payment_date, 3, 1) AS INT64), 
SAFE_CAST(SUBSTR(payment_date, 1, 2) AS INT64)
)    
WHEN 8 THEN 
CASE 
WHEN EXTRACT(YEAR FROM date_ddmmyyyy) > 2000 THEN date_ddmmyyyy
ELSE date_yyyymmdd
END    
ELSE NULL
END) formatted_payment_date
FROM `project.dataset.table`, 
UNNEST([STRUCT<date_ddmmyyyy DATE, date_yyyymmdd DATE>(
SAFE.PARSE_DATE('%d%m%Y', payment_date), 
SAFE.PARSE_DATE('%Y%m%d', payment_date)
)])

您可以使用以下中的伪数据来测试和玩以上内容

#standradSQL
WITH `project.dataset.table` AS (
SELECT 1 id, '11112011' payment_date UNION ALL
SELECT 2,    '1112011' UNION ALL
SELECT 3,    '20111111' UNION ALL
SELECT 4,    '20112011' UNION ALL
SELECT 5,    '20110228'
)
SELECT id, payment_date, 
FORMAT_DATE('%d-%m-%Y', CASE LENGTH(payment_date) 
WHEN 7 THEN 
SAFE.DATE(
SAFE_CAST(SUBSTR(payment_date, -4) AS INT64), 
SAFE_CAST(SUBSTR(payment_date, 3, 1) AS INT64), 
SAFE_CAST(SUBSTR(payment_date, 1, 2) AS INT64)
)    
WHEN 8 THEN 
CASE 
WHEN EXTRACT(YEAR FROM date_ddmmyyyy) > 2000 THEN date_ddmmyyyy
ELSE date_yyyymmdd
END    
ELSE NULL
END) formatted_payment_date
FROM `project.dataset.table`, 
UNNEST([STRUCT<date_ddmmyyyy DATE, date_yyyymmdd DATE>(
SAFE.PARSE_DATE('%d%m%Y', payment_date), 
SAFE.PARSE_DATE('%Y%m%d', payment_date)
)])
ORDER BY id

结果为：

Row id  payment_date    formatted_payment_date   
1   1   11112011        11-11-2011   
2   2   1112011         11-01-2011   
3   3   20111111        11-11-2011   
4   4   20112011        20-11-2011   
5   5   20110228        28-02-2011