SQL Server date comparisons based on month and year only

You can filter the month and year of a given date to the current date like so:

SELECT * 
FROM tableName 
WHERE month(date2) = month(getdate()) and year(date2) = year(getdate())

Just replace the GETDATE() method with your desired date.


First, I'd use a format for the dates that is unambiguous, like the standard 'YYYYMMDD' and not the '6/15/2014' you have been using. Aaron Bertrand's blog explains far better than I could, the various ways this can go wrong:
Bad habits to kick : mis-handling date / range queries

For the specific problem, your last query which finds the first and the last days of the months (for date1 and date3), is in my opinion on the right track. You only need though the first days of months (first day of date1 and first day of next month for date3), if you avoid the evil BETWEEN: What do BETWEEN and the devil have in common?

SELECT * 
FROM tableName 
WHERE date2 >= DATEADD(month, DATEDIFF(month, '19000101', @date1), '19000101')
  AND date2 < DATEADD(month, 1+DATEDIFF(month, '19000101', @date3), '19000101') ;

The query works as it is, no matter the datatype of date2 (DATE, DATETIME, DATETIME2 or SMALLDATTEIME).

Bonus point, indexes on date2 will be considered by the optimizer this way.


Improvement, according to (yet, another) Aaron's blog post, to avoid a problem with cardinality estimation when evaluating expressions with DATEDIFF():
Performance Surprises and Assumptions : DATEDIFF

SELECT * 
FROM tableName 
WHERE date2 >= CONVERT(DATE, DATEADD(day, 1 - DAY(@date1), @date1))
  AND date2 < DATEADD(month, 1, 
                      CONVERT(DATE, DATEADD(day, 1 - DAY(@date3), @date3))) ;

You can join on MONTH and YEAR values of those dates:

SELECT * 
FROM tableName 
WHERE YEAR(@date1) = YEAR(@date2) AND MONTH(@date1) = MONTH(@date2)

To handle inequalities, such as between, I like to convert date/times to a YYYYMM representation, either as a string or an integer. For this example:

DECLARE @date1 DATETIME = CAST('6/14/2014' AS DATETIME),
        @date2 DATETIME = CAST('6/15/2014' AS DATETIME),
        @date3 DATETIME = CAST('7/1/2014' AS DATETIME);

SELECT * FROM tableName WHERE @date2 BETWEEN @date1 AND @date3;

I would write the query as:

SELECT *
FROM tableName
WHERE year(@date2) * 100 + month(@date2) BETWEEN year(@date1) * 100 + month(@date1) AND
                                                 year(@date3) * 100 + month(@date1);