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);