title: SQL How to determine if date month date contains 29,30 or 31
body: <p>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.</p>
  
  <p>For example, the next Month effective date is showing fine as in this example
  '2011-04-20'.</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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.</p>
  
  <p>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'</p>
  
  <p>Many thanks for your help!!!</p>