SQL Query to find the last day of the month after 6 months

Question

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

eg. if I have a check date 07/15/2015 I want the next check date to be 01/31/2016

The verification date can be any day of any month.

Any recommendations would be appreciated.

Answer 1

If you are using SQL Server 2012+, you can use the eomonth function to get the last day of the 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.

Answer 2

.

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

Answer 3

declare @FindLastDay datetime
set @FindLastDay=CONVERT(varchar(10),DATEADD(M,6,'2015-07-15'),120)
 SELECT CAST(DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, @FindLastDay) + 1, 0)) as DATE)

:

2016-01-31

Answer 4

Select DateAdd(D, Day(DateAdd(Month, 7, '2015-07-15')) * -1, DateAdd(M, 7, '2015-07-15'))

.