Trying to find 1000 days ago from today

Hello experts,
Trying to find 1000 days ago from today using SQL in Oracle. Please let me know do you agree with this query:

SELECT TO_CHAR
    (TRUNC(SYSDATE)-1000) "Thousand Days Ago"
     FROM DUAL;
sukhoi35Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please use this -

One of the easiest and best method is sysdate-no of days.. We can directly subtract 1000 from the sysdate.

select sysdate "current date", sysdate - 1000 "Thousand Days Ago" from dual\\     

Open in new window


OUTPUT

	current date	        Thousand Days Ago
1	11.12.2017 04:53:33	17.03.2015 04:53:33

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sukhoi35Author Commented:
Thank you Pawan. Also, if I need to find 1000 days ago from say 05/Dec/2017, can I please know the best way to find it?
0
Pawan KumarDatabase ExpertCommented:
Yes we can use below for that-

Select to_date('2017/12/05','yyyy/MM/dd') - 1000 "1000 days back from 2017/12/05" from dual

Open in new window


OUTPUT

 	1000 days back from 2017/12/05
1	11.03.2015 00:00:00

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Pawan KumarDatabase ExpertCommented:
Or you can use this..

SELECT TO_DATE('05/Dec/2017','dd/Mon/YYYY', 'NLS_DATE_LANGUAGE = American') - 1000 "1000 days back" FROM DUAL\\
 
OUTPUT

1000 days back
11.03.2015 00:00:00
0
Pawan KumarDatabase ExpertCommented:
>>Please let me know do you agree with this query:
SELECT TO_CHAR
    (TRUNC(SYSDATE)-1000) "Thousand Days Ago"
     FROM DUAL;

Yes I agree with this query if you do not want time. This is fine to go with. Also note that The Oracle/PLSQL TO_CHAR function converts a number or date to a string. So for display purpose we can go with TO_CHAR but for calculation purpose please date itself.

Other options i have already given are given below-

SELECT TRUNC(sysdate - 1000) "Thousand Days Ago" from dual\\

SELECT TRUNC(TO_DATE('05/Dec/2017','dd/Mon/YYYY', 'NLS_DATE_LANGUAGE = American') - 1000) "Thousand Days Ago" from dual\\
0
Pawan KumarDatabase ExpertCommented:
Please refresh the page. Updated my last comment. Thanks.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Removed all zones other than Oracle to discourage any non-Oracle answers.
1
Mark GeerlingsDatabase AdministratorCommented:
As Pawan suggested, Oracle supports doing arithmetic on date values.  You can simply subtract any number of days from any Oracle date column or value.

Whether your query should include "trunc" or not depends on two things:
1. Does your data include the time-of-day or not?
2. Do you want to go back to exactly the same time of day in the past as the current time-of-day when you run this, or do you want to include all records from that day regardless of the current time-of-day and regardless of the times on the records?
0
sukhoi35Author Commented:
Thank you very much for your time and efforts in helping me clarify things!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.