BigQuery SQL WHERE Date Between Current Date and -15 Days

Question

I am trying to code the following condition in the WHERE clause of SQL in BigQuery, but I am having difficulty with the syntax, specifically date math:

WHERE date_column between current_date() and current_date() - 15 days

This seems easy in MySQL, but I can't get it to work with BigQuery SQL.

JohnHC · Answer 1 · 2016-12-20T14:00:59.807

50

Use DATE_SUB

select * 
from TableA
where Date_Column between DATE_SUB(current_date(), INTERVAL 15 DAY) and current_date()

Remember, between needs the oldest date first

edited Dec 20 '16 at 14:00

answered Dec 20 '16 at 13:47

JohnHC

10,935
1
24
40

2

I get this error `No matching signature for operator BETWEEN for argument types: DATETIME, DATE, DATE. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) ` – Urvah Shabbir Mar 27 '20 at 16:45
2

@urwaCFC I had the same issue. My guess is date_column is not type date, in my case it was type Timestamp. Try: Date(date_column) between current_date()... – eps May 01 '20 at 18:29
2

@UrvahShabbir for timestamp you can use the similar operator than date, for example: `WHERE Date_Column BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 15 DAY) AND CURRENT_TIMESTAMP()` – Jhon Mario Lotero Dec 14 '20 at 15:50

score 13 · Answer 2 · answered Dec 20 '16 at 13:47

13

You should probably switch the two around - the syntax should be the following:

WHERE date_column BETWEEN DATE_ADD(CURRENT_DATE(), -15, 'DAY') AND CURRENT_DATE()

answered Dec 20 '16 at 13:47

Siyual

16,415
8
44
58

2

The DATE_ADD version works. DATE_SUB is not supported in bigquery – Eric Hendershott Dec 20 '16 at 15:33
2

DATE_SUB version now works if you use standard SQL in bigquery. – Jas Feb 07 '18 at 22:12

score 2 · Answer 3 · edited Apr 15 '19 at 13:33

2

This works for me.

WHERE DATE(date_column) BETWEEN DATE(DATE_ADD(CURRENT_DATE(), -15, 'DAY'))
AND CURRENT_DATE()

edited Apr 15 '19 at 13:33

Sebastián Palma

32,692
6
40
59

answered Jun 28 '18 at 06:41

ppk28

367
4
6

BigQuery SQL WHERE Date Between Current Date and -15 Days

3 Answers3