• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 91
  • Last Modified:

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;
0
sukhoi35
Asked:
sukhoi35
5 Solutions
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now