当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - truncate date to week and compare against existing variable

问题描述:

I have a column called Actuals_Date and I want to insert dates into a table on a rolling basis such that the previous 6 weeks and next 8 weeks are loaded into the table.

For the previous 6 weeks, I have the following sql structure (from Tableau)

[Actuals Date] > DATEADD('week',-6,DATETRUNC('week', TODAY()) - 1) and [Actuals Date] <= DATETRUNC('week', TODAY()) - 1

and for the next 8 weeks, following syntax from Tableau:

[Forecast Date] >= DATETRUNC('week', TODAY()) - 1

and [Forecast Date] <= DATEADD('week', 7,DATETRUNC('week', TODAY()) - 1)

can someone please help me convert this into a sql query?

网友答案:

One way to accomplish your goal would be to use DATEADD()

Previous 6 Weeks:

SELECT f.MyColumns
FROM dbo.MyTable f
WHERE f.MyDateField BETWEEN DATEADD(WEEK,-6,CAST(GETDATE() AS DATE)) AND CAST(GETDATE() AS DATE)

Next 8 Weeks:

SELECT f.MyColumns
FROM dbo.MyTable f
WHERE f.MyDateField BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(WEEK,8,CAST(GETDATE() AS DATE))

Note: This assumes that f.MyDateField is a date without time. If f.MyDateField has a time, you will want to CAST it as DATE

SELECT f.MyColumns
FROM dbo.MyTable f
WHERE CAST(f.MyDateField AS DATE) BETWEEN DATEADD(WEEK,-6,CAST(GETDATE() AS DATE)) AND CAST(GETDATE() AS DATE)
分享给朋友:
您可能感兴趣的文章:
随机阅读: