DATEADD计算 [英] DATEADD Calculation

问题描述

在DATEADD（）

添加月份

SELECT '2012-01-29' AS [Date], CAST(DATEADD(MONTH, 1, '2012-01-31') AS DATE) AS NextDate
UNION
SELECT '2012-01-31' AS [Date], CAST(DATEADD(MONTH, 1, '2012-01-31') AS DATE) AS NextDate
UNION
SELECT '2013-01-31' AS [Date], CAST(DATEADD(MONTH, 1, '2013-01-31') AS DATE) AS NextDate

结果

减去月份

SELECT '2012-02-29' AS [Date], CAST(DATEADD(MONTH, -1, '2012-02-29') AS DATE) AS PrevDate
UNION
SELECT '2012-03-01' AS [Date], CAST(DATEADD(MONTH, -1, '2012-03-01') AS DATE) AS PrevDate

结果

当我添加一个月/ code>的日期 29,30,31，我得到的结果与 February 29 相同。对于减去，对于日期 29 Feb'2012 ，它显示 29 Jan'2012 。没有办法获取日期 30& 2012年1月31日。

When I add a Month for the dates 29,30,31 of Jan'2012, I get the same result as February 29. For subtract, for the date 29 Feb'2012, it shows 29 Jan'2012. There is no way to get the dates 30 & 31 of Jan'2012.

我想知道一些简短的解释。

I want to know some brief explanation.

推荐答案

行为在 DATEADD ：

DATEADD (datepart , number , date )

...

...

如果 datepart 是月，而日期月份比返回月份和日期

If datepart is month and the date month has more days than the return month and the date day does not exist in the return month, the last day of the return month is returned. For example, September has 30 days; therefore, the two following statements return 2006-09-30 00:00:00.000:

SELECT DATEADD(month, 1, '2006-08-30');

SELECT DATEADD(month, 1, '2006-08-31');

---

至于为什么它有这种行为，归因于可变长度的月份意味着在执行日期数学时必须应用某种形式的权衡，并且没有一个正确的答案存在。你认为1月31日是1月的最后一天还是1月1日之后的30天。这些都是正确的思考第31次的方法。但是，如果您将 1月更改为 2月，则现在可以获得两个不同的日期 - 2月28日或29日为最后二月份或三月二日或三日为二月一日之后30天。

---

As to why it has this behaviour, it all comes down to the fact that variable length months mean that you have to apply some form of tradeoff when performing date maths, and no one "correct" answer exists. Do you think of 31st January as being "the last day of January" or "30 days after the 1st day of January". Both of those are correct ways of thinking about the 31st. But if you change January to February, you now obtain two different dates - 28th or 29th of February for "the last day of February" or 2nd or 3rd of March for "30 days after the 1st day of February".

但功能必须只返回一个值。

But functions have to return just one value.

我不是说SQL Server应用上述任何一种解释。但是，它确实可以确保，如果您在特定日期添加1个月，则可以确定生成日期在下个月。

I'm not saying that SQL Server applies either of the above interpretations. What it does do, though, is ensures that if you add, say, 1 month onto a particular date, you can be sure that the resulting date falls in the following month.

这篇关于DATEADD计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！