BigQueryでstring型の文字列からdate型の日付に変換する

URL
bq_string2date
Created
2021/1/31 8:17
Tags
先日なぜかstring型の文字列で格納された年月日を、date型に戻して使う必要があったのでメモ。
今回の場合は、”20180101″みたいにハイフンなしで格納していた。
結論、parse_date使えば一番楽にできそう。
select
 parse_date('%Y%m%d', "20180101")
SQL
 
 
※以下、以前のメモ
文字列の切り出し
→ 間にハイフンを追加
→ 日付に変更
という考え方で変換。
SELECT
  CAST(CONCAT(SUBSTR('20180101', 1, 4), '-', SUBSTR('20180101', 5, 2), '-', SUBSTR('20180101', 7, 2) ) as DATE)
SQL
 
関数の構成について、順番に。
まず内側のSUBSTR関数は、以下のように用いることで、string型の文字列の開始文字数から切り出す文字数分だけ、文字列を取得することができる。
SUBSTR('文字列', 開始文字数, 切り出す文字数)
SQL
次に一番外側のCAST関数は、BigQueryにおいてデータ型を変換して返してくれる。
今回の肝となる関数であり、’as DATE’と指定することによって、以下のように文字列を日付に変更する。
ただし、間がハイフンで区切られている必要がある。
CAST ('YYYY-MM-DD' as DATE)
SQL
 
最後に、今回の文字列にはハイフンが入っていないため、CONCAT関数を使用して文字列を連結する。
CONCAT関数は括弧内でカンマ区切りの文字列を結合してくれるので、切り出した、
・YYYY(年、’20180101’の1文字目から4文字)
・ハイフン
・MM(月、’20180101’の5文字目から2文字)
・ハイフン
・DD(日、’20180101’の7文字目から2文字)
を連結してCASTの中に入れる。
以上のステップを持ってして、’20180101’の文字列で格納されたデータが日付型に変換された。
ちなみにCAST関数は、標準SQLでのみサポートされるため、従来UIの“Use Legacy SQL”のチェックは外して用いること。