Compare Months of Two Fields in Where Clause

Transact-SQL
1

Is it possible to compare the month part of two different date fields in a where clause like the below query?

SELECT ORDER_NO, CSNAME, ITEM#, TYPE, FLAG, STARTDATE, ISSUEDATE, BOARDCOST, QTY, ITTICKETNO, ITSOURCE, COMPLETEDATE
FROM rpttemp
WHERE (DATEPART(MM, rpttemp.COMPLETEDATE) = DATEPART(MM, rpttemp.ISSUEDATE)) AND FLAG = 'C'

Thank you,
Stacy

2

What you have written is perfectly legal SQL code and will work. However, you have to keep in mind that it looks ONLY at the month, and not the year. So if rpttemp.COMPLETEDATE has a value of April 15, 2015 and the corresponding value for rpttemp.ISSUEDATE is April 19, 2001, that row will pass the filter criterion based on the fact that both are in April.

If you are trying to compare the year also - i.e., you want the filter to pass only if the two dates are in the same month of the same year, you need something more. Example:

WHERE   ( DATEPART(MM, rpttemp.COMPLETEDATE) = DATEPART(MM, rpttemp.ISSUEDATE) )
		AND ( DATEPART(YY, rpttemp.COMPLETEDATE) = DATEPART(YY, rpttemp.ISSUEDATE) )
        AND FLAG = 'C'
3

Another way to do James's solution

WHERE (dateadd(mm, datediff(mm, 0, rpttemp.COMPLETEDATE), 0) = dateadd(mm, datediff(mm, 0, rpttemp.ISSUEDATE), 0) ) AND Flag = 'C'

This uses the first day of the month.

1 Like
4

Hi James,

Thanks for the reply. I only need it to check the month so that is fine, however it doesn't work. I know there are records in there with the same month and it doesn't return anything. I will try djj55's solution and see if I can get it to work. Any ideas why this wouldn't work?

Thanks,
Stacy

5

Do those records also have FLAG = 'C'?

I would debug it as follows:

SELECT TOP 10  FLAG, COMPLETEDATE,ISSUEDATE  FROM rpttemp; -- do you get 10 rows?

SELECT TOP 10 FLAG, COMPLETEDATE,ISSUEDATE FROM rpttemp
WHERE FLAG = 'C' -- do you get any rows?
-- if you do see if there are any that have the same month?

SELECT TOP 10 FLAG, COMPLETEDATE,ISSUEDATE FROM rpttemp
WHERE FLAG = 'C' 
AND DATEPART(MM, COMPLETEDATE) = DATEPART(MM, ISSUEDATE);
1 Like
6

Hi

Using sample data i am able to get it

Please see below

DROP TABLE #temp

CREATE TABLE #temp (
 id INT NULL ,
 flag VARCHAR(1) NULL ,
 completedate DATETIME NULL ,
 issuedate DATETIME NULL )


INSERT INTO #temp

SELECT 1 ,
       'Y' ,
       '2010-10-09' ,
       '2009-10-15'
INSERT INTO #temp

SELECT 1 ,
       'C' ,
       '2001-05-18' ,
       '2015-05-27'
INSERT INTO #temp

SELECT 1 ,
       'C' ,
       '2000-02-15' ,
       '2005-02-10'
INSERT INTO #temp

SELECT 1 ,
       'C' ,
       '2012-02-08' ,
       '2005-10-10'

SELECT * FROM #temp 

SELECT *
FROM #temp a
WHERE DATEPART(MM, a.COMPLETEDATE) = DATEPART(MM, a.issuedate)
  AND a.flag = 'C'