Between Operator Big Query Standard SQL

Question

Using Standard SQL in BQ - as part of a task I want to search for records created between 2pm the previous day & 2pm on current day

I have found

SELECT DATETIME_SUB(DATETIME_TRUNC(CURRENT_DATETIME(), DAY), INTERVAL 10 hour) Gives me 2PM yesterday

SELECT DATETIME_ADD(DATETIME_TRUNC(CURRENT_DATETIME(), DAY), INTERVAL 14 hour)

Gives me 2pm today

So, i assumed i could use this in my query

Select * from  
TableA  
where CreatedDate Between  
 DATETIME_SUB(DATETIME_TRUNC(CURRENT_DATETIME(), DAY), INTERVAL 10 hour) and DATETIME_ADD(DATETIME_TRUNC(CURRENT_DATETIME(), DAY), INTERVAL 14 hour)

However I get the following

No matching signature for operator BETWEEN for argument types: TIMESTAMP, DATETIME, DATETIME. Supported signature: (ANY) BETWEEN (ANY) AND (ANY)

Where am i going wrong?

You have a low rate. Important on SO, you have to mark accepted answers by using the tick on the left of the posted answer, below the voting. This will increase your rate. See how this works by visinting this link: meta.stackoverflow.com/questions/5234/… — Pentium10

Pentium10 Pentium10 · Accepted Answer · 2018-09-11T11:40:11

Your issue is that CreatedDate is TIMESTAMP and you need to convert into a DATETIME

It could be like:

where DATETIME(CreatedDate) Between ...

But you could easily write your own statements for TIMESTAMP

SELECT timestamp_sub(timestamp_trunc(current_timestamp() , DAY),interval 10 hour)

Between Operator Big Query Standard SQL

1 Answers