blocks|key|3939255|text|如果使用的是SQL+Server，则可以使用datefromparts()|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3939256|select+datefromparts(1950,+month(getdate()),+day(getdate())|code-block|syntax|javascript|3939257|或者，从当前年份开始添加若干年：|3939258|select+dateadd(year,+-4,+convert(date,+getdate()))|3939259|它们在闰日的行为可能略有不同，但您需要指定您希望在那一天发生什么。|3939260|entityMap^0|M|F|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|Y|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|R|$]]

If you are using SQL Server, you can use <code>datefromparts()</code>:

<pre><code>select datefromparts(1950, month(getdate()), day(getdate())
</code></pre>

or, add a number of years from the current year:

<pre><code>select dateadd(year, -4, convert(date, getdate()))
</code></pre>

These might have slightly different behavior for leap days, but you would need to specify what you want to happen on that day.

blocks|key|3935477|text|如果您尝试将4个月零1天添加到GetDate()中，请尝试以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3935478|选择DATEADD(MM，4，DATEADD(DD，1，GETDATE()|3935479|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

If you are trying to add 4 months and 1 day to GetDate(), try this:

select DATEADD(MM, 4, DATEADD(DD, 1, GETDATE() ))

blocks|key|3939290|text|之所以会得到这样的输出，是因为DATEADD解释整数的方式。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3939291|DATEADD的第三个参数应该是一个完整的日期，但按照原样，它提取的是GETDATE()的YEAR值，目前是2019年。DATEADD将这个数字视为自“第0天”以来的2,019天。在引擎盖下的第0天是1900-01-01+00:00:00.000，2019天后是1905-07-13+00:00:00.000。你的内在DATEADD增加了一天，将你带到7月14日，然后你的外部DATEADD增加了四个月，产生了你所看到的1905-11-14+00:00:00.000。|3939292|如果你感兴趣的是改变年份，你可以直接使用：|3939293|SELECT+DATEADD(YEAR,<Whatever+your+offset+value+or+values+are>,GETDATE());|code-block|syntax|javascript|3939294|为了获得，比如说，未来五年的价值：|3939295|SELECT+DATEADD(YEAR,X,GETDATE())+AS+NextFive
FROM
++(VALUES+(1),(2),(3),(4),(5))+AS+y(X);|3939296|结果：|3939297|%2B-------------------------%2B
%7C++++++++NextFive+++++++++%7C
%2B-------------------------%2B
%7C+2020-06-04+14:07:56.800+%7C
%7C+2021-06-04+14:07:56.800+%7C
%7C+2022-06-04+14:07:56.800+%7C
%7C+2023-06-04+14:07:56.800+%7C
%7C+2024-06-04+14:07:56.800+%7C
%2B-------------------------%2B|3939298|entityMap^0|F|7|0|0|7|Z|9|19|4|1O|7|2S|N|3N|N|4F|7|58|7|5U|N|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|11|8|@$9|12|A|13|B|C]|$9|14|A|15|B|C]|$9|16|A|17|B|C]|$9|18|A|19|B|C]|$9|1A|A|1B|B|C]|$9|1C|A|1D|B|C]|$9|1E|A|1F|B|C]|$9|1G|A|1H|B|C]|$9|1I|A|1J|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1K|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|1L|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|1M|8|@]|D|@]|E|$]]|$1|Q|3|R|5|L|7|1N|8|@]|D|@]|E|$M|N]]|$1|S|3|T|5|6|7|1O|8|@]|D|@]|E|$]]|$1|U|3|V|5|L|7|1P|8|@]|D|@]|E|$M|N]]|$1|W|3|-4|5|6|7|1Q|8|@]|D|@]|E|$]]]|X|$]]

The reason you're getting the output you are is because of the way that <code>DATEADD</code> interprets integers. 

The third argument for <code>DATEADD</code> should be a full date, but as written it's extracting the <code>YEAR</code> value of <code>GETDATE()</code>, which is currently 2019. <code>DATEADD</code> is treating that number as two thousand and nineteen days since "day 0". Day 0, under the hood, is <code>1900-01-01 00:00:00.000</code>, and 2019 days later is <code>1905-07-13 00:00:00.000</code>. Your inner <code>DATEADD</code> is adding one day to that, to take you to July 14th, and then your outer <code>DATEADD</code> is adding four months, resulting in the <code>1905-11-14 00:00:00.000</code> that you're seeing.

If all you're interested in doing is shifting the year, you can just use:

<pre><code>SELECT DATEADD(YEAR,&lt;Whatever your offset value or values are&gt;,GETDATE());
</code></pre>

To get, say, the next five years worth:

<pre><code>SELECT DATEADD(YEAR,X,GETDATE()) AS NextFive
FROM
 (VALUES (1),(2),(3),(4),(5)) AS y(X);
</code></pre>

Results:

<pre><code>+-------------------------+
| NextFive |
+-------------------------+
| 2020-06-04 14:07:56.800 |
| 2021-06-04 14:07:56.800 |
| 2022-06-04 14:07:56.800 |
| 2023-06-04 14:07:56.800 |
| 2024-06-04 14:07:56.800 |
+-------------------------+
</code></pre>

blocks|key|3935565|text|我已经知道怎么做了，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3935566|我取了过去的一年，'1900-01-01'，他们只是执行了一个DATEDIFF()函数来返回自该日期以来的年数：|3935567|DATEDIFF(YY,+'1900-04-01',+GETDATE())|code-block|syntax|javascript|3935568|使用这个，我可以将结果与常量年'1900‘加上我想要的常量月(04)和日(01)+(所以是'1900-04-01')相加，以产生我想要的结果，这很容易做到，只需将常量日期放入带有上述DATEDIFF()语句的DATEADD()语句中：|3935569|SELECT+DATEADD(YY,+DATEDIFF(YY,+'1900-04-01',+GETDATE()),+'1900-04-01')|3935570|这将产生以下结果：|3935571|2019-04-01+00:00:00.000|3935572|其中，2019是由GETDATE()语句生成的脚本运行的当前年份，04-01是输出应始终所需的月份和日期。|3935573|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

I've figured out how to do it,

I took a past year, '1900-01-01', and them simply did a DATEDIFF() to return the number of years since that date:

<pre><code>DATEDIFF(YY, '1900-04-01', GETDATE())
</code></pre>

Using this I could then add the result to the constant year '1900' plus my desired constant month (04) and day (01) (so '1900-04-01') to produce my desired result, this is easily done by putting the constant date in a DATEADD() statement with the DATEDIFF() statement above:

<pre><code>SELECT DATEADD(YY, DATEDIFF(YY, '1900-04-01', GETDATE()), '1900-04-01')
</code></pre>

This then produces the result of:

<pre><code>2019-04-01 00:00:00.000
</code></pre>

Where 2019 is the current year that the script is being run produced by the GETDATE() statement, and 04-01 is the desired month and day that the output should always be.

I need to make a SQL script return the same day and month but different with a different year for each year.

I have tried to use <code>DATEADD</code> to add the month and day to a standalone year that is in the form of <code>YEAR(GETDATE())</code>.

<pre><code>SELECT DATEADD(MM, 4, DATEADD(DD, 1, YEAR(GETDATE())))
</code></pre>

When I run this it is returning <code>1905-11-14 00:00:00.000</code> even when I replace the <code>GETDATE()</code> with an actual date, the <code>DATEADD()</code>'s are definitely working as when I change the numbers inside of them the output changes correspondingly.

--Edit--

The desired output I need from it is for the month and day to stay constantly at mm= 04 and dd= 01, however I need the year to be equal to the current year that it is being run.

So basically like this:

(Current year)-04-01

How to I add a standalone month to a standalone year in SQL?

SQL Server

我需要让一个SQL脚本返回相同的日期和月份，但不同的年份为每年。我尝试使用DATEADD将月份和日期添加到YEAR(GETDATE())形式的独立年份中。SELECT DATEADD(MM, 4, DATEADD(DD, 1, YEAR(GETDATE())))当我运行它时，它返回的是1905-11-14 00:00:...

问如何在SQL中将独立月份添加到独立年份？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL中将独立月份添加到独立年份？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL中将独立月份添加到独立年份？
EN