Home » SQL & PL/SQL » SQL & PL/SQL » SQL - select/delete data date older than today(3 days) (Oracle)
SQL - select/delete data date older than today(3 days) [message #327939] Wed, 18 June 2008 07:17 Go to next message
xmisuper
Messages: 4
Registered: June 2008
Junior Member
Would you pls teaching me how to select /delete data where datadate is older than 3 days of today from table, the date format is yyyy-mm-dd.

tabel A
name pdate status
abc 2008-06-01 P
abc 2008-06-04 P
bcd 2008-06-16 A
def 2008-06-17 P

i would like to delete those date older than 3 days of SYSDATE (today's date is 2008-06-18)

output:
name pdate status
bcd 2008-06-16 A
def 2008-06-17 P

any way to do that? thanks a lot
Re: SQL - select/delete data date older than today(3 days) [message #327941 is a reply to message #327939] Wed, 18 June 2008 07:20 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Refer these links.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8005.htm#SQLRF01505

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924

Regards

Raj
Re: SQL - select/delete data date older than today(3 days) [message #327955 is a reply to message #327939] Wed, 18 June 2008 07:40 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
where pdate < SYSDATE - 3
Re: SQL - select/delete data date older than today(3 days) [message #327956 is a reply to message #327941] Wed, 18 June 2008 07:42 Go to previous messageGo to next message
xmisuper
Messages: 4
Registered: June 2008
Junior Member
thanks Raj

how about the code below:
DELETE * A
   WHERE pdate < SYSDATE -3
Re: SQL - select/delete data date older than today(3 days) [message #327957 is a reply to message #327956] Wed, 18 June 2008 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on the datatype of your column.

And you should go back to DELETE page to review the syntax.

Regards
Michel

[Updated on: Wed, 18 June 2008 07:44]

Report message to a moderator

Re: SQL - select/delete data date older than today(3 days) [message #327958 is a reply to message #327955] Wed, 18 June 2008 07:44 Go to previous messageGo to next message
xmisuper
Messages: 4
Registered: June 2008
Junior Member
oh olivia, u wrote faster than me..
anyway, thanks a lot. Laughing
Re: SQL - select/delete data date older than today(3 days) [message #327983 is a reply to message #327958] Wed, 18 June 2008 09:13 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Delete from table_name where pdate < sysdate -3


As suggested by Michel,go back to DELETE page to review the syntax
Re: SQL - select/delete data date older than today(3 days) [message #327992 is a reply to message #327983] Wed, 18 June 2008 09:48 Go to previous messageGo to next message
xmisuper
Messages: 4
Registered: June 2008
Junior Member
the syntax : delete from <table name> where <expression>;

am i wrong?
Re: SQL - select/delete data date older than today(3 days) [message #327998 is a reply to message #327992] Wed, 18 June 2008 10:16 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
xmisuper wrote on Wed, 18 June 2008 07:48
the syntax : delete from <table name> where <expression>;

am i wrong?

Immediately above is correct; but that is NOT what you posted earlier in this thread.

Below is missing the keyword FROM
Quote:

DELETE * A
WHERE pdate < SYSDATE -3

Re: SQL - select/delete data date older than today(3 days) [message #328000 is a reply to message #327998] Wed, 18 June 2008 10:19 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FROM is optional but * is definitively too much.

Regards
Michel
Previous Topic: outer join problem
Next Topic: How to delete rows from parent table without scanning child tables?
Goto Forum:
  


Current Time: Mon Dec 05 15:15:54 CST 2016

Total time taken to generate the page: 0.12877 seconds