SQL to return all records 18 months ahead of another date (to end of month)

Question

I have a table with two DATETIME fields, OpeningDate and FinishingDate.

I am trying to write a query that returns all records that have a FinishingDate that is exactly 16 months ahead of the OpeningDate.

I have tried:

SELECT * FROM Sales
WHERE FinishingDate = DATEADD(MONTH, 16, OpeningDate)

But it appears to give me all records that fall between those dates (only up to the day in the month), I need it to get all records up until the end of the month.

So lets say I have an OpeningDate of 2017-10-6 (year, month, day) 18 months ahead would be 2019-04-06. This will only include records up to the 6th of April, I need it to include records up until the end of April.

What modifications can I make to the query to achieve this?

Any help would be great.

Thanks!

Answer 1

You can use this code,

SELECT * FROM Sales 
WHERE FinishingDate between OpeningDate AND 
DATEADD(MONTH, 18, EOMONTH(OpeningDate))

Note :

DATEADD(MONTH, 18, EOMONTH(OpeningDate))

EOMONTH find the end of the OpeningDate and then DATEADD will add 18 months to that date. That will give exactly the end date of 18th month from that date.

Answer 2

You can simply try this

SELECT * FROM Sales
WHERE FinishingDate = DATEADD(MONTH, 16, Convert(datetime,OpeningDate)) 

I tried it on my SQL server and it works fine with me.