-2

I have a table similar to one below. I'm trying to select only the rows where the Start Date is in the current month. Here is what I have so far, but it's not working.

SELECT *
FROM TABLE1
WHERE StartDate = MONTH(getdate())

How can I select only the values where the start date is in the current month?

user3147424
  • 3,022
  • 5
  • 19
  • 22

2 Answers2

4

Use this construct to avoid functions on the StartDate columns (like MONTH or YEAR). These functions will prevent any index or statistics being used/

SELECT *
FROM TABLE1
WHERE
  StartDate >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
 AND StartDate < DATEADD(month, 1+DATEDIFF(month, 0, GETDATE()), 0)

Any answer that puts a function on StartDate will not scale as expected. See error number 2 here. The filter is now non-sargable, and index/statistics can't be used. Every row will be looked at for a table scan.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    Personally I prefer `DateAdd(mm, DateDiff(mm, 0, Current_Timestamp) + 1, 0)` as this puts the `+1` as part of the `DateAdd()` which makes it a bit clearer in my opinion. Other than that, best answer here :) – gvee Mar 03 '14 at 15:39
  • @gbn can you explain so I can understand for future use? – user3147424 Mar 03 '14 at 17:14
  • 1
    The query optimiser needs to see "raw data" (without functions or processing) to generate a good query plan. So instead of using MONTH() and YEAR() we change it to a ranged search. The DATEADD/DATEDIFF is an efficient way to find start of week/month/day etc as per http://stackoverflow.com/a/1177529/27535 (and following links) – gbn Mar 03 '14 at 18:04
-1

You need to check the month of both fields

WHERE MONTH(startdate) = MONTH(getdate())
Jonno_FTW
  • 8,601
  • 7
  • 58
  • 90