CODEWITHSUNDEEP
sqloracle-database
user1024858
user1024858

Reputation: 129

Date not valid for month specified

I have a problem when running this Oracle SQL statement:

SELECT * 
  FROM tbl_content
 WHERE last_updated >= (systimestamp - INTERVAL '1' month(1)) 
 ORDER BY last_updated desc

And this error:

    java.sql.SQLException: ORA-01839: date not valid for month specified

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:742)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:212)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:951)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1053)
    at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:835)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1123)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3284)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3328)
    at com.vtdd.sms.content.xskt.XsktService.getKQXSFollowArea(XsktService.java:4044)
    at com.vtdd.sms.content.xskt.XsktService.getMessages(XsktService.java:421)
    at com.vht.sms.content.ContentAbstract.getSubmitMessages(ContentAbstract.java:47)
    at com.vht.sms.content.ContentFactory.getSubmitMessages(ContentFactory.java:335)
    at com.vht.sms.content.ContentFactory.run(ContentFactory.java:62)
    at java.lang.Thread.run(Thread.java:662)

Could you tell me what is wrong?

Upvotes: 0

Views: 3952

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

What is actually wrong is that interval arithmetic doesn't adjust days - see the 6th bullet in the link:

When interval calculations return a datetime value, the result must be an actual datetime value or the database returns an error.

ADD_MONTHS does; as that link says:

If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month.

So, ADD_MONTHS(DATE '2011-12-31', -1) gives you 2011-11-30, while DATE '2011-12-31' - INTERVAL '1' MONTH tries to give you 2011-11-31, which as the message says, isn't a valid date.

(It's debatable if this behaviour is actually wrong; it's unexpected, but I believe it's conforming to ANSI. There may be times you want it to work this way, though I can't think of any...)

Upvotes: 4

Ben
Ben

Reputation: 52863

Firstly, why are you using systimestamp? If you want this to the month then surely sysdate is exact enough? Secondly, I like - i.e. it's personal preference - to make it extremely clear what's happening. Oracle has an add_months function, which will do what you want. So your query could easily be:

SELECT * 
  FROM tbl_content
 WHERE last_updated >= add_months(sysdate, -1) 
 ORDER BY last_updated desc

Upvotes: 7

Related Questions