SQL 如何确定日期月份日期是否包含 29,30 或 31 [英] SQL How to determine if date month date contains 29,30 or 31

问题描述

这就是我想要做的.我需要在交易日期上加一个月，以便下个月生效在开始生效日期的同一天开始.如果起始生效日是 (30, 31) 不在下个月(例如 2 月 28 日)，则应将(3 月 1 日)作为下一个生效日期.交易日期简单来说就是知道用哪个月来加一个月.

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.

例如，下个月生效日期显示正常，如本例所示'2011-04-20'.

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)

但是，如果@StartEffectiveDate 是 '2011-01-31'，@transactiondate = '2011-01-30'，那么下个月生效日期的结果应该是 '2011-03-01' 因为 2011-02-31' 是日期无效.

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.

如何检查下个月日期是否有开始生效日.在这个例子中，如何检查二月是否有 31.如果它没有 31，那么它应该显示 '2011-03-01'

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

推荐答案

添加一个月后，比较 DAY.如果因为 DATEADD 转到月底而减少(例如 1 月 31 日至 2 月 28 日)，则跳到下个月

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

这里的 DATEADD/DATEDIFF 跳到下个月的月初

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

只需要添加额外的一天而不是一些花哨的 DATEADD/DATEDIFF...

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

这篇关于SQL 如何确定日期月份日期是否包含 29,30 或 31的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！