Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

calculate date difference

Hi,

I need to delete all email that are more than 30 days.

Delete from Email
where datediff(NOW(),PostDate) > 30

any idea why my query is not deleting?


Thanks,
0
lulu50
Asked:
lulu50
  • 9
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would go this way, to enhance performance:
Delete from Email
where PostDate < NOW() - 30


apart from that: is your field PostDate defined as datetime or as text field?
0
 
lulu50Author Commented:
angelIII:

I am using MySql

I am having problem with the date getting inserted  then delete all emails more than 30 days.

Please, tell me what I am doing wrong I cannot insert the date to do the 30 days different

the field is called PostDate

            INSERT INTO `lulu50_ChurchesBulletin`.`Email` (
                  `UserID`,
                  `UserName`,
                  `From`,
                  `Subject`,
                  `PostDate`,
                  `Body`,
                  `EmailStatus`
                  )
                  VALUES (
                  '#session.AdsUserID#', '#cookie.MemUser#', '#Trim(FORM.TxtFrom)#',
                  '#Trim(FORM.TxtSubject)#', 'now()', '#Trim(FORM.input)#','i');
0
 
lulu50Author Commented:
angelIII:

I am able to insert right now

I removed 'Now()'  to Now()
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
lulu50Author Commented:
angelIII:

Delete from Email
where PostDate < NOW() - 30

it is deleting all my emails
0
 
lulu50Author Commented:
PostDate is defined as date
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, you solved the ' issue for the insert ...

now:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-sub
Delete from Email
where PostDate < DATE_SUB(NOW(), INTERVAL 30 DAY)

Open in new window

0
 
lulu50Author Commented:
I set the date value to my record to be 2011-12-29

but I am still seeing the record.

the type for PostDate is Date
0
 
lulu50Author Commented:
How DATE_SUB(NOW() display the date?

my field display the date like this 2011-12-29
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
2011-12-29 is in the future, not in the past.
I understand you want to delete data older than 30 days?
0
 
lulu50Author Commented:
yes I do want it to be in the future


ok, please help me explain:

a user comes to my site sends an email I take the date when the user sends the email calculated to what every the current date and if the difference more than 30 days I want to delete their email.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not clearer to me ...

let's speak with data samples:
it's today 2011-10-28.
I am your customer, I want to send an email with your site.
the email is stored with today's date?

so, 30 days later (2011-11-27), you want this email to be deleted, right?
so, you have to consider yourself to be on that date, so there you want to delete emails that are 30 days old, aka created 30 days before "now()"

so, my suggestion should work.
0
 
lulu50Author Commented:
I got it to work !!!!!


Thank you
0
 
lulu50Author Commented:
Thank you
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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