WHERE ap_CreatedDate BETWEEN DATEADD(DAY,1,EOMONTH(GETDATE(),-2)) AND EOMONTH(GETDATE(),-1)

the above query will return the last/previous month, however its missing 2 records from the last day of the last month (2022-04-30) due to the date including a time range:

2022-04-30 09:16:00.000
2022-04-30 19:11:02.907

I'm currently manually pulling the dates

where ap_CreatedDate >= '2022-04-01' and ap_CreatedDate < '2022-05-01'

but I want to automate this process, any help will be much obliged

CodePudding user response：

The clearest and least error prone way to do this is as follows:

• Use greater then equals >= for the lower limit
• Use less than < for the upper limit, and make that limit the first of the next month

WHERE ap_CreatedDate >= DATEADD(DAY, 1, EOMONTH(GETDATE(), -2))
AND ap_CreatedDate < DATEADD(DAY, 1, EOMONTH(GETDATE(), -1))

• Don't use between because its not intuitive what it covers and you can end up with this issue