Q
Problem solve Get help with specific problems with your technologies, process and projects.

Date minus number

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

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close