英文:
Databricks SQL syntax for previous six months in where statement
问题
我正在尝试找出在Databricks的SQL查询的WHERE语句中查找过去六个月数据的方法，但是在语法方面遇到了很多问题。
现在我有：
Select * from table
where datediff(add_months(date_column, -6), date_column) = 1
这个查询没有报错，但没有返回任何结果。
英文:
I'm trying to figure out how to look for data in the last six months in the where statement of a SQL query in Databricks, but I'm having a lot of issues with the syntax.
Right now I have:
Select * from table
where datediff(add_months(date_column, -6), date_column) = 1
The query doesn't throw an error, but returns no results.
答案1
得分: 2
我认为你对DATEDIFF的期望是错误的。DATEDIFF告诉你两个日期之间的天数。在你的情况下，你正在将date_column与date_column减去6个月进行比较。这总是会得到6个月或约180天。
尝试这样做。
WHERE date_column > DATEADD(MONTH, -6, CURRENT_DATE())
也就是说，如果你的日期列大于当前日期减去6个月。
英文:
I think you're expecting the wrong thing from datediff. Datediff tells you the number of days between two dates. In your case, you're comparing your date_column to your date_column - 6 months. That's always going to be 6 months or ~180 days.
Try this.
WHERE date_column > DATEADD(MONTH, -6, CURRENT_DATE())
AKA, where your date column is greater than the current date minus 6 months.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论