かもメモ

自分の落ちた落とし穴に何度も落ちる人のメモ帳

Googleスプレッドシート QUERY関数の条件(WHERE)に日付を使いたい。

Spreadsheet Google Drive Tips

スプレッドシートのIMPORTRANGE関数とQUERY関数の組み合わせ便利ですね。
データがまとまったシートを作成しておいて、必要な情報をだけを表示させるのに使っています。
今回 日付を条件にしてデータを取得しようとしてハマったのでメモします。

例えばA列に日付がはいっているシートを取得し日付が2010年1月15日以降のデータを取得したいとします。A列はCol1です
下記のような書き方をしても#VALUE!になってしまい取得できません。

SELECT * WHERE Col1 > '2010-01-15'

日付の入ったセルをWHERE文で使うには date 'YYYY-MM-DD'の形式にする

Query Language Reference (Version 0.7)  |  Charts  |  Google Developers

Examples: 

where salary >= 600
where dept != 'Eng' and date '2005-01-21' < hireDate
where (dept<>'Eng' and isSenior=true) or (dept='Sales') or seniorityStartTime is null

A列が2010年1月15日以降のデータを取得したい場合は下記のようにします。

SELECT * WHERE Col1 > date '2010-01-15'

条件の日付を変数にしたい場合

"& A1 &"のフォーマットでセルの値をqueryに埋め込めるので、これを参考に

SELECT * WHERE Col1 > date '"& A1 &"'

としても#VALUE!になってしまい取得できませんでしたξ(Ծ‸Ծ)ξ
セルに入力された日付を使う場合は明示的にYYYY-MM-DDの形にフォーマットしたテキストにしてあげる必要がありました。 TEXT()を使うことでフォーマットしたテキストにすることができます。

TEXT(数値, 表示形式)

  • 数値 - 変換する番号、日付、時刻です。
  • 表示形式 - 変換する数値の表示形式を、二重引用符で囲って指定します。

TEXT - Docs Editors Help

クエリを下記のように修正します。
条件の日付をセルに入力された日時にする方法

SELECT * WHERE Col1 > date '"& TEXT(A1, "YYYY-MM-DD") &"'

これでは意図したとおりに取得することができました ζ*'ヮ')ζ

年だけ・月だけ・日にちだけを条件にしたい

年月日ではなく、年だけ、月だけ、日にちだけを条件にする場合は日付の入っているセルをYEAR()、MONTH()、DAY()で変換してあげれば簡単です。

年で検索

SELECT * WHERE YEAR(Col1) = 2010'

検索条件を変数にする

SELECT * WHERE YEAR(Col1) = "& A1 &"'

検索条件がYYYY-MM-DDの形式になっている

SELECT * WHERE YEAR(Col1) = "& TEXT(A1, "YYYY") &"'

月で検索

WHERE文中のMONTH()は1月が0になるので注意が必要です。

month()
Note: the months are 0-based, so the function returns 0 for January, 1 for February, etc.

Query Language Reference (Version 0.7)  |  Charts  |  Google Developers

SELECT * WHERE MONTH(Col1) + 1 = 12'

検索条件を変数にする

SELECT * WHERE MONTH(Col1) + 1 = "& A1 &"'

検索条件がYYYY-MM-DDの形式になっている

SELECT * WHERE MONTH(Col1) + 1 = "& TEXT(A1, "MM") &"'

TEXT(A1, "M")でもOK

日で検索

SELECT * WHERE DAY(Col1) = 18'

検索条件を変数にする

SELECT * WHERE DAY(Col1) = "& A1 &"'

検索条件がYYYY-MM-DDの形式になっている

SELECT * WHERE DAY(Col1) = "& TEXT(A1, "DD") &"'

TEXT(A1, "D")でもOK

　
サンプル: ソビエト連邦の指導者リスト - Google スプレッドシート 　

[参考]

スマホアプリ「アイカツ!フォトonステージ!!」シングルシリーズ01「カメレオントーク★」

スマホアプリ「アイカツ!フォトonステージ!!」シングルシリーズ01「カメレオントーク★」