CODEWITHSUNDEEP
sqloracle-databasedate
Andy
Andy

Reputation: 3

Oracle SQL Retrieve dates within 2 weeks

I'm trying to return information on cars in my table 'Cars' which have MOT's due within the next 2 weeks. MOTDUE has been defined as to_date('2015-03-25', 'yyyy-mm-dd');

SELECT * FROM Cars WHERE MOTDUE = CURRENT_DATE + INTERVAL '2' WEEK;

just gives me "missing or invalid datetime field" error.

Thanks.

Upvotes: 0

Views: 5485

Answers (3)

David Faber
David Faber

Reputation: 12486

There are two types of INTERVALs in Oracle - YEAR TO MONTH and DAY TO SECOND. So YEAR, MONTH, DAY, HOUR, MINUTE and SECOND are all valid increments. WEEK, unfortunately, is not (nor is FORTNIGHT, sadly). The ANSI standard way of doing this would be something like the following:

SELECT * FROM cars
 WHERE motdue >= CURRENT_DATE
   AND motdue < CURRENT_DATE + INTERVAL '14' DAY;

The problem with the above is that in Oracle, the ANSI-standard CURRENT_DATE doesn't adhere to the ANSI standard - it has a time portion just like SYSDATE. That's fine if you don't want to truncate dates at midnight.

Upvotes: 1

Boneist
Boneist

Reputation: 23578

Depending on what you mean by "within the next two weeks" and assuming that the datatype of the MOTDUE column is DATE, then the following ought to give you what you need (or at least, allow you to amend appropriately):

select *
from   cars
where  motdue < trunc(sysdate) + 14
and    motdue >= trunc(sysdate);

Upvotes: 2

starko
starko

Reputation: 1149

Try this:

SELECT * FROM Cars WHERE MOTDUE = CURRENT_DATE + INTERVAL '14' DAY;

Upvotes: 1

Related Questions