date - 如何在 BigQuery 中将多种字符串格式转换为一种日期格式？

Question

我的数据中有一个日期列显示“付款日期”，该列具有多种字符串格式，例如 ddmmyyyy、ddmyyyy 和 yyyymmdd。有谁知道如何将所有这些转换为统一的日期格式，例如 BigQuery 中的 dd-mm-yyyy ？

Answer 1

以下示例适用于 BigQuery 标准 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