CODEWITHSUNDEEP
sqlsql-serversql-server-2008date
Andy Evans
Andy Evans

Reputation: 7176

SQL Server date comparisons based on month and year only

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?

Upvotes: 30

Views: 124468

Answers (7)

Mirza Baig
Mirza Baig

Reputation: 21

This is a classic math problem. Its easy to compare months with months. That is multiply year with number of months in a year and add month of the year to it. This will give an integer which can be compared in a range.

Ex: July 2021 would be 07+12*2021

Upvotes: 2

Lee-UK
Lee-UK

Reputation: 21

I rolled my dates up to the same date using EOMONTH() and compared them. So, if I had date1 as 09/18/2019 and date2 as 09/16/2019 they would both roll up to 09/30/2019. It isn't a direct answer to your question, but it worked in my situation.

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

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

Upvotes: 8

Andy Ferdian
Andy Ferdian

Reputation: 41

You can use formatting your date like this 'yyyyMM' , so only the month in the same year will be selected.

SELECT * 
FROM tableName 
WHERE FORMAT(date_month_bill, 'yyyyMM') < FORMAT(DATEADD(MONTH, -1, GETDATE()), 'yyyyMM') 
AND FORMAT(date_month_bill, 'yyyyMM') > FORMAT(DATEADD(MONTH, -3, GETDATE()), 'yyyyMM')

Upvotes: 3

DavidG
DavidG

Reputation: 118937

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

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

Upvotes: 14

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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

Upvotes: 23

Tanner
Tanner

Reputation: 22733

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.

Upvotes: 29

Related Questions