CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
Nidhin_toms
Nidhin_toms

Reputation: 737

adding months to a date 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;

Upvotes: 1

Views: 18374

Answers (3)

user330315
user330315

Reputation:

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)

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

Alternatively you could use:

datesold + INTERVAL '1' month

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

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

Another thing:

I am trying to add months to an existing date in SQL.

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

Upvotes: 6

Salman
Salman

Reputation: 19

This can be used to add months to a date in SQL:

select DATEADD(mm,1,getdate())

This might be a useful link.

Upvotes: 0

Ben Graham
Ben Graham

Reputation: 2099

Your SQL has typographical quotation marks, not standard ones. E.g. ’ is not the same as '. Instead of delimiting a string value, those quotes become part of the value, at least for the particular SQL I have here to test with.

If this doesn't fix your problem, try posting the error you're getting in your question. Magical debugging isn't possible.

Upvotes: 0

Related Questions