SQL How to determine if date month date contains 2

Question

This is what I am trying to do. I need to add one month to the transaction date so that the next month effective date start on the same day as the starting effective date. If the starting Effective day is (30, 31) is not in the next month (eg. feb 28), then it should give (march 1st) as a next effective date. The transaction date is simply to know which month to use to add a month.

For example, the next Month effective date is showing fine as in this example '2011-04-20'.

declare @StartEffectiveDate datetime
declare @transactiondate datetime
declare @NextMonthEffectivedate  datetime

set @StartEffectiveDate = '2011-01-20'
set @transactiondate = '2011-03-14'

--calculating next month effective date. incremening transactiondate by 1, but on same day --as the starting effective date.

set @NextMonthEffectivedate = dateadd(month,month(@transactiondate)-month(@StartEffectiveDate)+1,@Starteffectivedate)

But, if the @StartEffectiveDate is on '2011-01-31', @transactiondate = '2011-01-30', then the result for Next month effective date should be '2011-03-01' because 2011-02-31' is not a valid date.

How to check if next month date has the starting effective day or not. In this example, how to check if Feb has 31 or not. If it doesn't have 31, then it should show '2011-03-01'

Many thanks for your help!!!

Answer 1

This SQL statement gives last date of a month, you just need to replace GETDATE() with your desired datetime variable or your column name.

DECLARE @lastDateOfMonth AS INT= DATEPART(DD, DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)));