Search code examples
sqlsql-servert-sqlbetweendateadd

SQL to return records using two date fields that fall 16 months ahead of each other

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 know how to write the query to check between two dates (with SQL BETWEEN and DATEADD), but i'm not sure how to do this query.

I have tried:

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

But it appears to give me all records that fall between those dates, and not just those that are exactly 16 months ahead.

Any help would be great.

Thanks!

Solution

  • Unless I'm missing something, FinishingDate exactly 16 months ahead of OpeningDate: 

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