visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
262 experts, 1140 registered users, 1616 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

We now celebrate our 5 years of existence and break our frequentation records... Here are the stats of those 5 years.

Databases :: Oracle :: oracle problem with date arithmetic (adding months)
By: Nono Great Britain  Date: 11/04/2008 09:46:00  English  Points: 20 Status: Answered
Quality : Excellent
I've currently got an Oracle view that requires minimal maintenance each new month but I'm sure there is a way of coding it to be maintenance free if only I can find a way of calculating the previous month.

Rather than coding the full view, I've extracted the problem code in question.

Based on SYSDATE = 30th June 2004 the following statement returns the values shown below (which it exactly what I require for my view)
SELECT TO_CHAR(SYSDATE, 'YYMM'),
TO_CHAR(SYSDATE, 'YYMM') -1,
TO_CHAR(SYSDATE, 'YYMM') -2,
TO_CHAR(SYSDATE, 'YYMM') -3
FROM DUAL

0406 405 404 403

However, if the SYSDATE = 30 January, 2004 (or any other date less than April) then the code fails with the following results:
0401 0400 399 398
whereas my desired results would by 0401 0312 0311 0310
By: Bernard Date: 11/04/2008 09:47:37 English  Type : Comment
solution :

SELECT TO_CHAR (SYSDATE, 'YYMM'), TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'YYMM'), TO_CHAR (ADD_MONTHS (SYSDATE, -2), 'YYMM'), TO_CHAR (ADD_MONTHS (SYSDATE, -3), 'YYMM') FROM DUAL

By: VGR Date: 11/04/2008 09:52:00 English  Type : Answer
your solution works, but only for months arithmetic ;-)

first the "from dual" is not SQL-compliant ;
second, SQL standard says SYSDATE should be a function and called as SYSDATE() ;
third, ADD_MONTH() provides a solution in here because you want to add or substract entire months, but Oracle hasn't a ADD_DAY() function for day arithmetic ;
and last, all this fuss comes from the fact that Oracle isn't SQL2 (SQL-92) compliant and doesn't support the INTERVAL feature :
oracle ne comprend pas INTERVAL (SQL-99)

Quoting the SQL99 standard BNF :


<interval type> ::= INTERVAL <interval qualifier>
<interval qualifier> ::= <start field> TO <end field> | <single datetime field>
<start field> ::= <non-second primary datetime field> [ <left paren> <interval leading field precision> <right paren> ]
<non-second primary datetime field> ::= YEAR | MONTH | DAY | HOUR | MINUTE
<interval leading field precision> ::= <unsigned integer>



SQL> select sysdate - interval 1 DAY from DUAL; select sysdate - interval 1 DAY from DUAL * ERREUR à la ligne 1 : ORA-00923: Mot-clé FROM absent à l'emplacement prévu



Real solution : use an SQL standard-compliant RDBMS like MySql ;-)

Do register to be able to answer

 Add This Article To:
 del.icio.usDel.icio.us  diggDigg  googleGoogle  spurlSpurl
 blinkBlink  wongWong  simpySimpy  yahooY! MyWeb 
EContact
browser fav
page generated in 4555.062060 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page