Home » SQL & PL/SQL » SQL & PL/SQL » delete date
delete date [message #355390] Thu, 23 October 2008 22:33 Go to next message
ejoeyz_85
Messages: 30
Registered: October 2008
Member
hello everyone,

Can someone help me how to create sql statements to delete the table
records and retain only 3 months of data. Concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_date month
is < current month – 3.

i tried this but its not working:

delete from gen_api_performance_daily
 where statistics_date < add_months(sysdate,-3);


and also how to create sql statements to delete the table records and
retain only 1 year of data.
And concept should be like this:

Delete all (TABLE_NAME) table records where the statistics_year is =
previous year – 1.

I have no idea about it..
Re: delete date [message #355391 is a reply to message #355390] Thu, 23 October 2008 22:46 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:

delete from gen_api_performance_daily
where statistics_date < add_months(sysdate,-3);


Why it is notr working ?

Or are you looking for

add_months(TRUNC( sysdate,'MON'),-3)



Quote:
Delete all (TABLE_NAME) table records where the statistics_year is =previous year – 1.


Are you sure you want to implement this logic ?

Smile
Rajuvan.
Re: delete date [message #355392 is a reply to message #355390] Thu, 23 October 2008 22:54 Go to previous messageGo to next message
ejoeyz_85
Messages: 30
Registered: October 2008
Member
yes yes, im sure to implement this logic.

because i want to delete the table records and retain only 1 year of data.
Re: delete date [message #355393 is a reply to message #355390] Thu, 23 October 2008 22:58 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

If you want to delete as exactltly as suggested by you .

Quote:

Delete all (TABLE_NAME) table records where the statistics_year is =previous year – 1.


In the Jan 2009 , you will be left with Only One month Data .

Anyway what did you try ?
what was the output ?
what you are expecting ?

Smile
Rajuvan.
Re: delete date [message #355395 is a reply to message #355390] Thu, 23 October 2008 23:02 Go to previous messageGo to next message
ejoeyz_85
Messages: 30
Registered: October 2008
Member
for example, i have year 2007 data and now for 2008 data. When for the next year 2009, the 2007 data will be deleted. So the table records and retain only 1 year of data (year 2008).
Re: delete date [message #355397 is a reply to message #355390] Thu, 23 October 2008 23:13 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

OK OK ..
Quote:

Anyway what did you try ?
what was the output ?
what you are expecting ?


Smile
Rajuvan.
Re: delete date [message #355399 is a reply to message #355390] Thu, 23 October 2008 23:15 Go to previous messageGo to next message
ejoeyz_85
Messages: 30
Registered: October 2008
Member
how to do that? could u help me?
Re: delete date [message #355401 is a reply to message #355390] Thu, 23 October 2008 23:18 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

show us what did you try . we may help you.

I think you got enugh clues and example Here

Smile
Rajuvan.
Re: delete date [message #355407 is a reply to message #355401] Thu, 23 October 2008 23:30 Go to previous messageGo to next message
ejoeyz_85
Messages: 30
Registered: October 2008
Member
i had tried


select sysdate,
add_months(TRUNC(sysdate,'year'),-12) start_date, 
trunc(add_months(sysdate,-1),'year') end_date
from dual


and got the result:

SYSDATE   START_DAT END_DATE
--------- --------- ---------
24-OCT-08 01-JAN-07 01-JAN-08


this is the way i can Sad
Re: delete date [message #355409 is a reply to message #355390] Thu, 23 October 2008 23:41 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Wont this be enough to delete records < 01-JAN-07

Or try like
SQL> select sysdate,
  2      add_months(TRUNC(sysdate,'year'),-24) start_date,
  3       add_months(TRUNC(sysdate,'year'),-12   )-1 end_date
  4  from DUAL;

SYSDATE   START_DAT END_DATE
--------- --------- ---------
24-OCT-08 01-JAN-06 31-DEC-06

SQL>


Smile
Rajuvan.
Previous Topic: cross join (merged)
Next Topic: Dump large tables...
Goto Forum:
  


Current Time: Wed Dec 07 02:42:07 CST 2016

Total time taken to generate the page: 0.10724 seconds