The last day of the previous month - BigQuery

Question

I'm trying to select rows where a timestamp field, recdate, has a date value up to and inclusive of the last completed day of the month. For example, as this is July 2016, I want all rows with date values up to and inclusive of 31-06-2016. This used to work fine in T-SQL, I'd use the following and assign it to @today and chuck that in my WHERE:

DECLARE @today DATETIME SELECT @today = CONVERT(VARCHAR(25),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-0,0)));

I'm struggling in BigQuery though, I can't get DATEDIFF or GETDATE to work, was wondering if anybody had thoughts on this?

best wishes

Dave

Answer 1

Another way with Standard SQL.

First day current month:

SELECT DATE_TRUNC(CURRENT_DATE(), MONTH)

Last day previous month (first day current minus 1):

SELECT DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)

First day next month:

SELECT DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH)

Last day current month (first day next month minus 1):

SELECT DATE_SUB(DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY)