Subtracting n Days from a date using SQL

sql oracle date-arithmetic oracle8i
MrMokari picture MrMokari · Sep 7, 2011 · Viewed 56.4k times · Source

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.

Answer

Lukas Eder picture Lukas Eder · Sep 7, 2011

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

Related questions

Oracle DateTime in Where Clause?

I have sql something like this: SELECT EMP_NAME, DEPT FROM EMPLOYEE WHERE TIME_CREATED >= TO_DATE('26/JAN/2011','dd/mon/yyyy') -> This returns 10 rows and TIME_CREATED = '26-JAN-2011' Now when i do this …

Read More
Oracle date "Between" Query

I am using oracle database. i want to execute one query to check the data between two dates. NAME START_DATE ------------- ------------- Small Widget 15-JAN-10 04.25.32.000000 PM Product 1 17-JAN-10 04.31.32.000000 PM select * from <TABLENAME> where start_date BETWEEN '15…

Read More
Oracle SQL Where clause to find date records older than 30 days

I want to find records in a (Oracle SQL) table using the creation date field where records are older than 30 days. It would be nice to find records using a operators like > but if anyone can suggest quick SQL …

Read More