GETDATE month in where clause

Question

I have this table:

Key_No	Import_Date
12345678	2023-09-01
98765432	2023-10-01
85231654	2023-11-01
00020102	2023-12-01

I want to find the data of import_date after this month.

I tried a SQL statement like this:

select * 
from Table1 
where DATEPART(mm, Import_date) = MONTH(GETDATE()) 
  and DATEPART(yyyy, import_date) = YEAR(GETDATE())

But I can only find the data for October. I need to show all data after the current month.

Answer 1

In my opinion, two things are important here:

1. We can simply use the function EOMONTH which returns the last day of the current month.
2. We should avoid to use functions in the WHERE clause. They will be called for every single row of the table and will likely slow down the query, escpecially when the table has many rows. We will do better to just declare a variable with the date. Then we check if the date from the table is later than this date or not. Thus, a function is executed once only and not a thousand times or even a million times.

So if the current month should not be included and we want to start with the first day of the next month, we can do this:

DECLARE @compareDate DATE
SET @compareDate = EOMONTH(GETDATE())
SELECT *
FROM table1
WHERE
  Import_Date > @compareDate

If also the current month should be included, this will do:

DECLARE @compareDate DATE
SET @compareDate = DATEADD(DAY,1,EOMONTH(GETDATE(),-1))
SELECT *
FROM table1
WHERE
  Import_Date >= @compareDate

Try out with some sample data here

I recommend to read this documentation that explains both the function EOMONTH and how to use a variable.