• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

Delete query

Hi,

I would like to delete records that have been in the database more than 12 months.

Delete from ADS
where datediff(NOW(),AdsDate) > twelve months?)  (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)
0
lulu50
Asked:
lulu50
1 Solution
 
Muhammad KhanManager, ITCommented:
Delete from ADS
where period_diff(NOW(),AdsDate) > 12)  (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)
0
 
lulu50Author Commented:
it deleted all my data

Delete from ADS
where (period_diff(NOW(),AdsDate) > 12) and (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)
0
 
lulu50Author Commented:
I only want to delete the date calculated from today's date up to 12 months
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CluskittCommented:
Delete from ADS
where datediff(NOW(),AdsDate) > 365 and  (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)
0
 
lulu50Author Commented:
Cluskitt:

GREAT!!
0
 
lulu50Author Commented:
thank you
0
 
qasim_mdCommented:
Try this and let me know if it helped :

Delete from ADS
where months_between (to_date (NOW(), 'yyyy/mm/dd'), to_date (AdsDate, 'yyyy/mm/dd') ) > 12
and (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)
0
 
Muhammad KhanManager, ITCommented:
oops ...sorry.. my bad.. Hope you are not doing it directly on production..



Delete from ADS
where date_add(AdsDate, interval 12 Month)<Now()  and (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)
0
 
Muhammad KhanManager, ITCommented:
while Cluskitt solution will work for most of the cases.. you may find it delete 1st of january record in a leap year due to 366 days in leap year..
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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