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

October 2020 Update (BigQuery Standard SQL)

BigQuery now support LAST_DAY function as well as arithmetic operations + and '-' for dates

So, now you can use below options to get last day of previous month

#standardSQL
select 
  date_trunc(current_date(), month) - 1, 
  last_day(date_sub(current_date(), interval 1 month)),
  date_sub(last_day(current_date()), interval 1 month)

with output (having in mind it is October 14 today)

me personally - i love the first option as least verbose and straightforward!

-- ~~~~~~~~~~~~~~~~~~~

Use below as an example (BigQuery Legacy SQL)

SELECT DATE(DATE_ADD(CURRENT_DATE() , -DAY(CURRENT_DATE()), "DAY"))

BTW, there are 30 days in June :o) - with exception of Priestley's "The Thirty-First of June"

Answer 2

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)

Answer 3

Works in standard SQL

SELECT DATE_ADD(CURRENT_DATE(), INTERVAL -EXTRACT(DAY FROM CURRENT_DATE()) DAY)

Answer 4

DATE_ADD( CURRENT_DATE(), INTERVAL -EXTRACT( DAY FROM CURRENT_DATE()) DAY)

That is the correct syntax for Google BigQuery.

Answer 5

Begining SQL 2012 there is a EOMONTH Function :

https://msdn.microsoft.com/en-us/library/hh213020.aspx

Usage:

DECLARE @date VARCHAR(255) = '07/01/2016';  
SELECT EOMONTH ( @date ) AS Result;  
GO  

Will return 07/31/2016

DECLARE @date VARCHAR(255) = GetDate() ;  -- To Get End of month for Current Month
SELECT EOMONTH ( @date ) AS Result;  
GO  

Will return 07/31/2016

My Bad .. I did not realize the OP was looking for last day of previous month

But this should work:

DECLARE @date Datetime = '12/31/2017' -- Input Date. Has to be Datetime NOT varchar. If incomming date is a varchar it needs to be converted to Datetime.
Select DAY( @date ) -- Returns 31
SELECT @date - DAY( @date ) as LastDayOfPrevMonth

Output:

LastDayOfPrevMonth

2017-11-30 00:00:00.000

I have tried it for most of the Edge cases like leap day , first/last day of any month etc.

Answer 6

Works with any version of SQL Server

SELECT convert(varchar(10),
    DATEADD(mm, -1, 
        DATEADD(s,-1,
            DATEADD(mm, 
                DATEDIFF(m,0,getdate())+1
            ,0)
        )
    ), 103) AS [LastMonthEnd]