上个月的最后一天 - BigQuery [英] The last day of the previous month - BigQuery
问题描述
我正在尝试选择时间戳字段 recdate 的日期值直到并包含该月最后一天完成的行.例如,由于这是 2016 年 7 月,我希望日期值不超过 31-06-2016 的所有行.这曾经在 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 Legacy 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屋!