Subtracting n Days from a date using SQL

Question

I am quite a beginner when it comes to Oracle. I am having trouble figuring out how to do something similar to this :

SELECT ID, NAME, TO_CHAR(DATEBIRTH, 'DD/MM/YYYY HH24:MI:SS') 
FROM PEOPLE WHERE DATEBIRTH >= ANOTHERDATE - NDAY

To put it short, I want to select everyone who were born N days before a specific date and time but I am not quite sure that this is the way to do it nor that it would give me the results I expect.

PS: I am developping under oracle8i.

Arabia

Oracle 8i ? Is that even supported any more? – Kluge

What prevented you from just trying that? – Distributive

@Kluge It do not think it is supported anymore but my client wouldn't upgrade. – Arabia

@Andrian. I am in a quite complicated situation, I cannot debug my queries without spending least 30mn for each try.. do not ask, took over a project with quite a special client. – Arabia

@MrMokari, I have added a little extra information to my answer, I hope it clarifies things. – Kluge

@Kluge thanks a lot ! I will try it out tomorrow, I am working on another project today :) – Arabia

Answer 1

Your query looks correct to me. That's how you subtract days from dates in Oracle. This link holds some more insight for you, should you want to add months or years:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1157035034361

Royo

Answer 2

You might want to consider the time portion of your date "ANOTHERDATE".

If you are only concerned with whole days then you could rewrite your query as:

SELECT ID, NAME, TO_CHAR(DATEBIRTH, 'DD/MM/YYYY HH24:MI:SS')
  FROM PEOPLE 
 WHERE DATEBIRTH >= TRUNC(ANOTHERDATE - NDAY)

N.B. This is assuming "NDAY" is a numeric.

"To put it short, I want to select everyone who were born N days before a specific date and time but I am not quite sure that this is the way to do it nor that it would give me the results I expect."

My first query would get you everyone with a birthday ON OR AFTER "NDAY" days before "ANOTHERDATE".

This will get you everyone who has a birthday ON the day that is "NDAY" days before "ANOTHERDATE":

SELECT id,
       name,
       TO_CHAR(datebirth, 'DD/MM/YYYY HH24:MI:SS') AS birth_date
  FROM people
 WHERE TRUNC(datebirth) = TRUNC(anotherdate - NDAY);

If there is an index on the "datebirth" column then you do not want to wrap it with TRUNC so you could use the following which would be able to use any index on "datebirth":

SELECT id,
       name,
       TO_CHAR(datebirth, 'DD/MM/YYYY HH24:MI:SS') AS birth_date
  FROM people
 WHERE datebirth >= TRUNC(anotherdate - NDAY)
   AND datebirth < (TRUNC(anotherdate - NDAY) + 1);

Kluge

You first query were more of what I was looking for! – Arabia

Answer 3

You have to convert (anotherdate) explicitly to date then subtract (ndays) from it:

SELECT ID, NAME, TO_CHAR(DATEBIRTH, 'DD/MM/YYYY HH24:MI:SS')
  FROM PEOPLE 
 WHERE DATEBIRTH >= TO_DATE(ANOTHERDATE) - NDAY

Chaconne

Answer 4

Below query subtract n days from given date

select to_date('date') - 'n days' 
from dual 

example:

select TO_DATE('02-Jul-16') - 90 
from dual

Sigismondo