SQL Query to find the last day of the month in 6 months time

Question

I am trying to write a bit of SQL that will give the last day of the month in 6 months time.

E.g. if I have of a date of inspection 15-07-2015 I want the next inspection date to be 31-01-2016

The date of inspection could be any day of any month

Any advise would be appreciated.

Answer 1

if i understand. that's it

declare @d date = '2015-07-15'
    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@d)+7,0))

Answer 2

If you are using SQL Server 2012+ you can use the eomonth function to get the last day of a month:

declare @d date = '2015-07-15'
select eomonth(@d,6)

result: 2016-01-31

The function takes a date and an optional integer that specifies the number of months to add to the date parameter.