将几个月添加到日期SQL [英] adding months to a date SQL

问题描述

我正在尝试在SQL中向现有日期添加月份.显示的新列将具有一个跟进列，而不是一个天列.我在选择语句中遇到错误.您能帮忙吗?

I am trying to add months to an existing date in SQL. The new column displayed will have a followup column instead of a days column. Im getting an error in the select statement.can u help?

Create table auctions(
item varchar2(50),
datebought date,
datesold date,
days number
);
Insert into auctions values (‘Radio’,’12-MAY-2001’,’21-MAY-2001’,9);
Select item,datebought,datesold,ADD MONTHS(datesold,3)"followup" from auctions;

推荐答案

您对add_months()函数的使用不正确.不是两个字，只有一个(带下划线)

Your usage of the add_months() function is incorrect. It's not two words, it's just one (with an underscore)

add_months(datesold, 1)

请注意ADD和MONTHS之间的下划线_.它是函数调用而不是运算符.

note the underscore _ between ADD and MONTHS. It's function call not an operator.

或者，您可以使用:

datesold + INTERVAL '1' month

尽管值得注意的是，带间隔的算术是有限的(如果没有中断的话)，因为它只是增加"了日期值的月份值.这可能会导致无效的日期(例如，从一月到二月).尽管这是记录的行为(请参见下面的链接)，但我认为这是一个错误(SQL标准要求这些操作"算术运算遵守与日期和时间相关的自然规则，并根据格里高利日历得出有效的日期时间或区间结果)

Although it's worth noting that the arithmetics with intervals is limited (if not broken) because it simply "increments" the month value of the date value. That can lead to invalid dates (e.g. from January to February). Although this is documented behaviour (see below links) I consider this a bug (the SQL standard requires those operations to "Arithmetic obey the natural rules associated with dates and times and yield valid datetime or interval results according to the Gregorian calendar")

有关详细信息，请参见手册:
http://docs.oracle.com/cd/E11882_01 /server.112/e26088/functions011.htm#i76717
http://docs.oracle.com/cd/E11882_01 /server.112/e26088/sql_elements001.htm#i48042

See the manual for details:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions011.htm#i76717
http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#i48042

另一件事:

我正在尝试在SQL中向现有日期添加月份.

然后为什么要使用INSERT语句?要更改现有行的数据，应使用UPDATE.所以看来您真正追求的是这样的:

Then why are you using an INSERT statement? To change the data of existing rows you should use UPDATE. So it seems what you are really after is something like this:

update auctions
   set datesold = add_months(datesold, 1)
where item = 'Radio';

这篇关于将几个月添加到日期SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！