SQL Server date comparisons based on month and year only

Question

I am having trouble determining the best way to compare dates in SQL based on month and year only.

We do calculations based on dates and since billing occurs on a monthly basis the date of the month has caused more hindrance.

For example

DECLARE @date1 DATETIME = CAST('6/15/2014' AS DATETIME),         @date2 DATETIME = CAST('6/14/2014' AS DATETIME)  SELECT * FROM tableName WHERE @date1 <= @date2 

The above example would not return any rows since @date1 is greater than @date2. So I would like to find a way to take the day out of the equation.

Similarly, the following situation gives me grief for same reason.

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've done inline conversions of the dates to derive the first day and last day of the month for the date specified.

SELECT *  FROM tableName  WHERE date2 BETWEEN     DATEADD(month, DATEDIFF(month, 0, date1), 0) -- The first day of the month for date1     AND     DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, date2) + 1, 0)) -- The lastday of the month for date3 

There has to be an easier way to do this. Any suggestions?

Answer 1

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

Answer 2

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.