上个月的最后一天-BigQuery [英] The last day of the previous month - BigQuery

问题描述

我正在尝试选择时间戳记字段recdate的日期值直到并包括该月的最后完成日期的行.例如，由于这是2016年7月，因此我希望所有日期值不超过2016年6月31日的行.过去在T-SQL中可以正常使用，我将使用以下内容并将其分配给@today并在我的WHERE中查到:

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

尽管我在BigQuery中挣扎，但我无法让DATEDIFF或GETDATE正常工作，想知道是否有人对此有所想法?

最美好的祝愿

戴夫

解决方案

2020年10月更新(BigQuery标准SQL)

BigQuery现在支持

我个人-我喜欢第一个选项，因为它不那么冗长和直接！

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

使用以下示例(BigQuery旧版SQL)

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

顺便说一句，六月有30天:o)-除了 Priestley的"；六月三十一日""

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

解决方案

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"

这篇关于上个月的最后一天-BigQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！