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