How do I construct a query for a date minus a number? I did this query, but it doesn't work.
-- 10 days before 12/25/1995 SELECT TO_DATE ('12-DEC-95 -10','MONTH DD, YYYY') FROM dual;
Quick review of relative dates in Oracle: Given a date d, you can compute the date n days earlier by saying d - n. You can get the date n days after d by saying d + n or n + d. d must be a DATE expression (not a string representing a date); n can be any number, positive or negative, not necessarily an integer. (For example, "-.5 + SYSDATE" is the time 12 hours (= .5 days) ago.)
Now let's apply the rules to this case. We need a DATE. Since there are no DATE literals in Oracle, we have to use some expression (like the TO_DATE function) to create one. Also, let's be consistent about date formats, even in comments. (In the example above, it's unclear if we're expecting to get December 15, 1995 or December 2, 2095.)
-- 10 days before 25-Dec-1995 SELECT TO_CHAR ( TO_DATE ( '25-Dec-1995', 'DD-Mon-YYYY' ) - 10, 'DD-Mon-YYYY' ) AS target_date FROM dual; TARGET_DATE ----------- 15-Dec-1995