Home » SQL & PL/SQL » SQL & PL/SQL » Date - Date (Oracle 9i)
Date - Date [message #345579] Thu, 04 September 2008 04:33 Go to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
Hi
I am new to orafaq.com.

Here is my script- I want to view records older than 3 days back.


SELECT distinct batchcartonid, adddate
FROM pickdetail P
WHERE NOT EXISTS
(
SELECT *
FROM cload C
WHERE C.caseid = P.batchcartonid
)
and P.batchcartonid <> ' '
order by adddate
-----------------------------------
It returns the following
----------------------------------
BATCHCARTONID ADDDATE
0000152775 01/09/2008 5:44:19 PM
0000152814 01/09/2008 5:44:20 PM
0000152669 01/09/2008 5:44:21 PM
0000152900 01/09/2008 5:44:22 PM
0000152900 01/09/2008 5:44:28 PM
0000153221 01/09/2008 5:44:44 PM
0000153155 01/09/2008 5:45:11 PM
0000152596 01/09/2008 5:45:24 PM
0000152581 01/09/2008 5:45:33 PM
0000152960 01/09/2008 5:45:39 PM
0000152929 01/09/2008 5:45:48 PM
0000152731 01/09/2008 5:45:56 PM
0000152929 01/09/2008 5:46:02 PM
0000152837 01/09/2008 5:46:10 PM
0000153188 01/09/2008 5:46:24 PM
0000152955 01/09/2008 5:46:27 PM
0000152955 01/09/2008 5:46:49 PM
0000153247 01/09/2008 5:46:55 PM
0000153247 01/09/2008 5:47:08 PM
0000152755 01/09/2008 5:47:12 PM
0000152755 01/09/2008 5:47:21 PM
0000152923 01/09/2008 5:47:25 PM
0000153162 01/09/2008 5:47:36 PM
0000153227 01/09/2008 5:47:41 PM
0000153227 01/09/2008 5:47:43 PM
0000153250 01/09/2008 5:47:44 PM
0000153391 01/09/2008 5:47:45 PM
0000153391 01/09/2008 5:47:52 PM
0000152841 01/09/2008 5:47:54 PM
0000153107 02/09/2008 1:05:07 PM
0000152817 02/09/2008 1:06:20 PM
0000153270 02/09/2008 1:06:38 PM
0000152910 02/09/2008 1:07:15 PM
0000152759 02/09/2008 1:07:33 PM
0000152813 02/09/2008 1:07:37 PM
0000152596 02/09/2008 1:08:08 PM.

I want it to show me all records older than 3 Days.

Regards
Re: Date - Date [message #345581 is a reply to message #345579] Thu, 04 September 2008 04:36 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Hint:- Sysdate

Regards,
Rajat
Re: Date - Date [message #345587 is a reply to message #345579] Thu, 04 September 2008 04:45 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
Like this

and P.adddate < sysdate - 3

it works
Re: Date - Date [message #345591 is a reply to message #345587] Thu, 04 September 2008 04:49 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
This will not give you results becuase you are comparing
time part also.

Oracle store date and time in one datatype i.e Date.
Oracle doesnot have different datatype for this like
in MySql you have date and datetime as different datatypes.

try :-

trunc(p.adddate)<trunc(sysdate-3)


Regards,
Rajat

[Updated on: Thu, 04 September 2008 04:50]

Report message to a moderator

Re: Date - Date [message #345632 is a reply to message #345591] Thu, 04 September 2008 06:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I feel you are mistaken.

The comparison shown will return all the records that are older than 3 days from the current point in time.

If you want to count only whole days, you could change the condition to
and P.adddate) < trunc(sysdate) - 3

but it most definitely will return rows in the form posted.
Previous Topic: how to fetch data from multiple databases
Next Topic: schema
Goto Forum:
  


Current Time: Tue Dec 06 02:57:17 CST 2016

Total time taken to generate the page: 0.14285 seconds