Home SQL to return all records 18 months ahead of another date (to end of month)
Reply: 2

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

Mark
1#
Mark Published in 2017-12-06 04:10:12Z

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!

Abhilash Ravindran C K
2#
Abhilash Ravindran C K Reply to 2017-12-06 05:31:24Z

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.

MohamedHamza
3#
MohamedHamza Reply to 2017-12-06 11:43:15Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.316935 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO