问题

I have a question about searching data by date period.

我的问题是关于如何按日期范围搜索数据。

The time period of my search is the 26th of a month to the 25th of a month.
我们的搜索时间范围是每月26日到每月25日。

We use Getdate() to determine the search data period.
我们使用Getdate()来确定搜索数据的时间段。

Example,

示例：

• getdate()=2023-06-20, we need to search data from 2023-05-26 to 2023-06-25

• getdate()=2023-06-20，我们需要搜索从2023-05-26到2023-06-25的数据。

• getdate()=2023-06-25, we need to search data from 2023-05-26 to 2023-06-25

• getdate()=2023-06-25，我们需要搜索从2023-05-26到2023-06-25的数据。

• getdate()=2023-06-26, we need to search data from 2023-06-26 to 2023-07-25

• getdate()=2023-06-26，我们需要搜索从2023-06-26到2023-07-25的数据。

• getdate()=2023-07-01, we need to search data from 2023-06-26 to 2023-07-25

• getdate()=2023-07-01，我们需要搜索从2023-06-26到2023-07-25的数据。

I had to try the following to search for current month data.
我尝试了以下方法来搜索当前月份的数据。

We will use triggers to do this.

我们将使用触发器来实现这一点。

Declare @CurrentDate datetime;
Set @CurrentDate='2023-06-29'
Select START_DATE=dateadd(month,0,DATEFROMPARTS(YEAR(@CurrentDate),MONTH(@CurrentDate),26))
Select END_DATE=dateadd(month,1,DATEFROMPARTS(YEAR(@CurrentDate), MONTH(@CurrentDate),25))

Result: START_DATE=2023-06-26, END_DATE=2023-07-25
结果：START_DATE=2023-06-26, END_DATE=2023-07-25

Or:

或者：

Declare @CurrentDate datetime;
Set @CurrentDate='2023-07-01'
Select START_DATE=dateadd(month,0,DATEFROMPARTS(YEAR(@CurrentDate),MONTH(@CurrentDate),26))
Select END_DATE=dateadd(month,1,DATEFROMPARTS(YEAR(@CurrentDate), MONTH(@CurrentDate),25))

Result: START_DATE=2023-07-26, END_DATE=2023-08-25
结果：START_DATE=2023-07-26, END_DATE=2023-08-25

We need to search the data period from 2023-06-26 to 2023-07-25 when Getdate() is between 2023-06-26 and 2023-07-25. You can see that if the date becomes 2023-07-01, the current date period is incorrect.
当Getdate()在2023-06-26和2023-07-25之间时，我们需要搜索从2023-06-26到2023-07-25的数据。您可以看到，如果日期变为2023-07-01，当前日期段是不正确的。

Declare @CurrentDate datetime;  Set @CurrentDate='2023-06-29'
Select    START_DATE=dateadd(month,0,DATEFROMPARTS(YEAR(@CurrentDate),MONTH(@CurrentDate),26))
Select END_DATE=dateadd(month,1,DATEFROMPARTS(YEAR(@CurrentDate), MONTH(@CurrentDate),25))`

Declare @CurrentDate datetime;
Set @CurrentDate='2023-07-01'
Select START_DATE=dateadd(month,0,DATEFROMPARTS(YEAR(@CurrentDate),MONTH(@CurrentDate),26)) 
Select END_DATE=dateadd(month,1,DATEFROMPARTS(YEAR(@CurrentDate), MONTH(@CurrentDate),25)) `

答案1

得分: 0

SELECT getdate,
CASE WHEN DAY(getdate) <= 25 THEN DATEADD(MONTH, -1, DATEADD(day, 26 - DAY(getdate), getdate)) ELSE getdate END START_DATE,
CASE WHEN DAY(getdate) > 25 THEN DATEADD(MONTH, 1, DATEADD(day, 25 - DAY(getdate), getdate)) ELSE DATEADD(day, 25 - DAY(getdate), getdate) END END_DATE
FROM a

SELECT getdate, 
CASE WHEN DAY(getdate) <= 25 THEN DATEADD(MONTH, -1, DATEADD(day, 26 - DAY(getdate), getdate)) ELSE getdate END START_DATE,
CASE WHEN DAY(getdate) > 25 THEN DATEADD(MONTH, 1, DATEADD(day, 25 - DAY(getdate), getdate)) ELSE DATEADD(day, 25 - DAY(getdate), getdate) END END_DATE
FROM a