SQL How to determine if date month date contains 29,30 or 31

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!!!

After adding a month, compare DAY. If less because DATEADD goes to end of the month instead (eg 31 Jan to 28 Feb), then skip to next month

The DATEADD/DATEDIFF here skips to the start of the following month

declare @StartEffectiveDate datetime
set @StartEffectiveDate = '2011-01-20'
SELECT
    CASE
        WHEN DAY(@StartEffectiveDate) <= DAY(DATEADD (MONTH, 1, @StartEffectiveDate)) THEN DATEADD (MONTH, 1, @StartEffectiveDate)
        ELSE DATEADD(day, 1, DATEADD (MONTH, 1, @StartEffectiveDate))
    END

set @StartEffectiveDate = '2011-01-31'
SELECT
    CASE
        WHEN DAY(@StartEffectiveDate) <= DAY(DATEADD (MONTH, 1, @StartEffectiveDate)) THEN DATEADD (MONTH, 1, @StartEffectiveDate)
        ELSE DATEADD(day, 1, DATEADD (MONTH, 1, @StartEffectiveDate))
    END

set @StartEffectiveDate = '2011-02-28'
SELECT
    CASE
        WHEN DAY(@StartEffectiveDate) <= DAY(DATEADD (MONTH, 1, @StartEffectiveDate)) THEN DATEADD (MONTH, 1, @StartEffectiveDate)
        ELSE DATEADD(day, 1, DATEADD (MONTH, 1, @StartEffectiveDate))
    END

Edit: only need to add an extra day rather then some fancy DATEADD/DATEDIFF...

This should work:

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

set @StartEffectiveDate = '20110131'
set @transactiondate = '20110130'

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

set @NextMonthEffectivedate = dateadd(month,DATEDIFF(month,@StartEffectiveDate,@transactionDate)+1,@StartEffectiveDate)

if DATEPART(day,@StartEffectiveDate) <> DATEPART(day,@NextMonthEffectiveDate)
begin
    --rounding occurred - next month isn't long enough.
    set @NextMonthEffectivedate = DATEADD(day,1,@NextMonthEffectiveDate)
end

print @NextMonthEffectivedate

select datepart(day, getdate())

This gets you the day of the month. So here's the full script, though I didn't understand what you're doing with @StartEffectiveDate so I just kept that the same:

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

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

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

set @NextMonthEffectivedate = 
    case
        when datepart(day, @transactiondate) > 28 then dateadd(month,2,dateadd(day,-1*(datepart(day, @transactiondate)-1),@transactiondate))
        else dateadd(month,month(@transactiondate)-month(@StartEffectiveDate)+1,@Starteffectivedate)
    end
select @NextMonthEffectivedate

Check last day of a month.

SELECT day(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))

Add 1 month in date.

SELECT DATEADD(month,13,getdate());

Check first day of the month

SELECT CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/' + 
                CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/01' AS DATETIME)

We can create a scalar function for this.

CREATE FUNCTION dbo.DaysInMonth(@date DATETIME) RETURNS int
AS
BEGIN
    RETURN (SELECT DAY(DATEADD(m, 1, @date - DAY(@date))))
END

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)));

SQL does not directly support this. You would need to create a function that checks for the correct days. See an example Here