"Is there any way to get the data between current date to yesterday date via query in Bigquery"

Question

"I am using query for getting data from current date to yesterday date in Bigquery but i am not able to get the yesterday date in BQ"

SELECT COUNT (*) 
FROM `urbn-edw-prod.UrbnProcessingErrors.RetailFeedDimensionError` 
WHERE errorReason = "Invalid Merchandise SKUs" 
AND DATE(ingestionTime) between CURRENT_DATE and DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)

this line of code is not giving yesterday date i think

DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)

I want to get the data from today till yesterday in BQ but i think i am using wrong query I expect output to be 500 for now but it is giving 0

Answer 1

You can use:

WHERE errorReason = 'Invalid Merchandise SKUs' AND
      DATE(ingestionTime) >=  DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) AND
      DATE(ingestionTime) < CURRENT_DATE())

Or more simply:

WHERE errorReason = 'Invalid Merchandise SKUs' AND
      DATE(ingestionTime) = CURRENT_DATE())

When you use BETWEEN the order of the last two operands matters. The smaller value is first, the larger value second.

Answer 2

I expect output to be 500 for now but it is giving 0

This is because you have wrong order in below line

AND DATE(ingestionTime) between CURRENT_DATE and DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)   

instead you should use

AND DATE(ingestionTime) between DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) and CURRENT_DATE    

this line of code is not giving yesterday date i think

DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) gives you yesterday - the issue was in order as explained above - but, you can also use below instead

DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)    

Also note, when you use BETWEEN - both sides of it are included, so if you need only today's data you can just use

AND DATE(ingestionTime) = CURRENT_DATE     

or for just yesterday - you can use

AND DATE(ingestionTime) = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)