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

      2019/01/13

こんにちは、データアナリストのTEN(@02smwhere)です。

先日なぜかstring型の文字列で格納された年月日を、date型に戻して使う必要があったのでメモ。

 
今回の場合は、”20180101″みたいにハイフンなしで格納していたので、

文字列の切り出し

→ 間にハイフンを追加

→ 日付に変更

という考え方で変換した。

 

スポンサーリンク

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

以下のコードで文字列を日付に変更できる。

関数の構成について、順番に解説していく。

 
まず内側のSUBSTR関数は、以下のように用いることで、string型の文字列の開始文字数から切り出す文字数分だけ、文字列を取得することができる。

SUBSTR('文字列', 開始文字数, 切り出す文字数)

 
次に一番外側のCAST関数は、BigQueryにおいてデータ型を変換して返してくれる。

今回の肝となる関数であり、’as DATE’と指定することによって、以下のように文字列を日付に変更する。

ただし、間がハイフンで区切られている必要がある。

 

CAST ('YYYY-MM-DD' as DATE)

 
最後に、今回の文字列にはハイフンが入っていないため、CONCAT関数を使用して文字列を連結する。

CONCAT関数は括弧内でカンマ区切りの文字列を結合してくれるので、切り出した

・YYYY(年、’20180101’の1文字目から4文字)

・ハイフン

・MM(月、’20180101’の5文字目から2文字)

・ハイフン

・DD(日、’20180101’の7文字目から2文字)

を連結してCASTの中に入れる。

 
以上のステップを持ってして、’20180101’の文字列で格納されたデータが日付型に変換された。

 
ちなみにCAST関数は、標準SQLでのみサポートされるため、従来UIの“Use Legacy SQL”のチェックは外して用いること。

 

 - データ分析